Find last row with data

[parry]

Hi, Im sure Ive seen something here to quickly find the last row with data in a column but cant see it now.

I have the following code ....
dim rngBank as range
For Each rngBank In ThisWorkbook.Worksheets("sheet1").Range("L:L")

Rather than range L:L I only want from L1 until last row with data in it.

I have previously assigned a variable to Selection.End(xlDown).Select and then found row for this then gone back to the top but I have seen people select the last row (65000+) in a statement that does this in one line.

Any ideas?

Thanks
Parry

 

[Juan Pablo González]

Dim Rng as Range

Set Rng = Range("L65536").End(xlUp)

'This will assign to Rng the last used range in Column L

 

[parry]

Thanks Juan! That was it. Unfortunately Im doing something wrong because I keep getting a runtime "error 1004:application-defined or object-defined error" on the ForEach line.

Dim rngBank As Range
Dim strBank As String
Dim rngBankLast As Range

Range("l1").Select
Set rngBankLast = Range("L65536").End(xlUp)
For Each rngBank In ThisWorkbook.Worksheets("sheet1").Range([rngBankLast])
strBank = rngBank.Value
AddZero strBank, 2
rngBank.Value = strBank
Next

Can you help?
Thanks
Parry

 

[parry]

Oops also forgot to say that when doing a debug the Set line has a value of 2 even though the last range is row 38.

Maybe thats why its coming up with an error because the range is only one row? Dont know.

Thanks again
Parry

 

[K_Man95]

parry,

not sure if this is what you want or not, but this is the program I got to find and select only the data in a specified Column. No matter how many rows you have, it always finds the top and bottom and selects everything in between.... so you may have to modify this a bit.....hope this helps:

Sub Selected_Range()

Dim wsSheet As Worksheet
Dim rnArea As Range

Sheets("Sheet2").Select

Set wsSheet = ThisWorkbook.Worksheets("Sheet2")

With wsSheet
Set rnArea = .Range(.Range("D3"), Range("D" & .Range("D65536").End(xlUp).Row))
End With

rnArea.Select

End Sub

 

[parry]

Superb! Thanks K_Man95.