Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

select range as in the first range without using scroll bar

0

Hello

the  code  will copy  data from userform to sheet  if  there is empty row  before TOTAL row , if  there is no empty row  then will insert row and copy data  from userform before TOTAL row . now  what  I search for it after copy  tho sheet  before TOTAL row  I want moving the  added row  as in  the  first row without  using scroll bar to  see the  last  data added before TOTAL row

based on example sheet  should  moving row 23 for  the  last  added data  above   as if it's row1 .

thanks guys

excel
MS Office Version: 2019
posted by: Abdo M (rep: 16) on Tue Aug 1, 2023 at 8:43 am
Answer
Discuss

Discussion

Hi again Abdo

Sorry but your new question makes no sense to me since:

1) your "GOODS" sheets is the same as the "example" sheet and
2) "should  moving row 23 for  the  last  added data  above   as if it's row1" seems strange (since row 1 is the headings, DATE, BRAND etc.).

Do you mean that the new data entered from the UserForm should be added to row 2 (rather then row 23) and other rows shifted down?

Please clarify by editing your original question.
John_Ru (rep: 6142) Aug 2, '23 at 1:30 pm
Hi John,
well, seem that's difficult to  explaining ,but  I  said  without  using  scroll bar.

    let's  take  example  if  you  add data in row  1000  of course  when  you enter  the  sheet   will  not  show  just  what  you  can  see  at row 24  what  you  should  do  to  see row1000? you  will  have  to  pull down  scroll bar   to  see data  at row 1000    what  look  for pull down row 1000 by  scroll bar automatically   to  bottom until   I  can  see without I  use  scroll bar  manually .
  so  when  add the  data before  TOTAL row  whatever  number  of  row  just  I  want  see the  last added data    when  I  said  move  , not replace  row  with  for  row  .

  all  of  thing is  relating to see  the row has added before TOTAL row  without I use scroll  bar to botatom  manually  so you  see  in EXAMPLE  shee is  the  same  thing  with  first  sheet surely yes , but  you  don't  note how  scroll bar move to  see last added data before TOTAL row ?

row 23 becomes as  if  it's   first  row , but  in  reality it's 23 . the  only  thing  which happened just move scroll bar to  bottom  to  see it.
Abdo M (rep: 16) Aug 2, '23 at 3:02 pm
Add to Discussion

Answers

0
Selected Answer

Abdo

I think (from your clarification in the Discussion under the Question) that you want to add new data above the TOTALs row (as before) but see that row in the active window (without needing to scroll to see it).

In the attached file, I've added a line which should so that (in bold in the code extract below):

    ' copy UserForm values to last row
    .Range("B" & LR) = Date
    .Range("C" & LR) = TextBox1.Text
    .Range("D" & LR) = TextBox2.Text
    .Range("E" & LR) = TextBox3.Text
    .Range("F" & LR) = TextBox4.Text
     ' reveal added row
    .Range("A" & LR).Show

Hope this is what you want. (Sorry but your chosen translation tool is doing a bad job of expressing what you want in English)

posted by: John_Ru (rep: 6142) on Wed Aug 2, 2023 at 5:44 pm
Discuss

Discussion

that  what I  look  for it, sorry  about  bad  expression !
thanks  very much .
Abdo M (rep: 16) Aug 3, '23 at 4:44 am
Great. Thanks for selecting my Answer, Abdo. 
John_Ru (rep: 6142) Aug 3, '23 at 5:45 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login