Macro-unknown num of lines

[juliekerin]

Hi there,

I am currently trying to write my first macro! I have written the macr
and it does what its meant to. However, every day the file the macr
works on is of different length and when i look into the VB part of th
macro in the code its only performing the action to 250 lines. Is ther
any way to set the macro to check how many lines are in the file fo
that day and then to run on that many lines? If that makes any sens
i'd be grateful for any help!
Thanks,

Juli

 

[Harald Staff]

Hi Julie

Something like this ?

Sub test()
Dim L As Long
L = Sheets(1).Cells(65000, 1).End(xlUp).Row
MsgBox "Last filled cell in A is A" & L
End Sub

HTH. best wishes Harald

 

[juliekerin]

Thanks, thats great, it gives the line but now I just don't know how t
use that information where I need it.

Eg. In this line Range("J2:J434").Select I want the second ref (J434
to be J and then the num of lines in the s/sheet. I've tried using &L t
reference but cant seem to get it right!

any ideas??
Sorry for the basic questions! am a complete novice

Thanks

Juli

 

[Harald Staff]

Hi Julie

Sub test()
Dim L As Long
L = Cells(65000, 10).End(xlUp).Row
Range("J2:J" & L).Select
End Sub

The 1 who became 10 here is column number, J is col 10. Note that you rarely
need to select things in code. But we can leave that for the next step of
learning.

HTH. best wishes Harald