VBA - Find last used row number within certain columns please

[harrycosh]

How can I find the last used range within certain columns. I know how to find the last used row number within a whole worksheet or a whole column, but I was looking for the last row number within columns G:L. The problem is I have data within columns A:F that I would like to ignore for this Macro.

Thanks

 

[shyrath]

Maybe not the most elegant solution. But for 3 columns it´s not to much code.

Find the last row in G, H, I then find the biggest number of the three. And build the range out of that.

Sub test()

Dim cntG As Long
Dim cntH As Long
Dim cntI As Long
Dim cntJ As Long
Dim cntK As Long
Dim cntL As Long

Dim rng As Range


cntG = Worksheets("Sheet2").Range("G65536").End(xlUp).Row
cntH = Worksheets("Sheet2").Range("H65536").End(xlUp).Row
cntI = Worksheets("Sheet2").Range("I65536").End(xlUp).Row
cntJ = Worksheets("Sheet2").Range("J65536").End(xlUp).Row
cntK = Worksheets("Sheet2").Range("K65536").End(xlUp).Row
cntL = Worksheets("Sheet2").Range("L65536").End(xlUp).Row

maxr = Application.Max(cntG, cntH, cntI, cntJ, cntJ, cntK, cntL)
Set rng = Worksheets("Sheet2").Range("G2:L" & maxr)

rng.Select

End Sub

 

[VoG]

Or perhaps

Dim LR As Long, x As Long, i As Integer
For i = 7 To 12
    x = Cells(Rows.Count, i).End(xlUp).Row
    If x > LR Then LR = x
Next i

 

[shyrath]

Or perhaps

Dim LR As Long, x As Long, i As Integer
For i = 7 To 12
    x = Cells(Rows.Count, i).End(xlUp).Row
    If x > LR Then LR = x
Next i

There´s always a shorter way Gotta love that!!

 

[Richard Schollar]

Hi

A non-looping alternative:

Dim r As Range

With ActiveSheet.Range("G:L")
  Set r = .Find(What:="*",After:=.Cells(1,1), SearchDirection:=xlPrevious, SearchOrder:=xlByRows, Lookin:=xlFormulas)
End With

If Not r Is Nothing Then MsgBox "Last Row in G:L is row " & r.Row

 

[harrycosh]

Thanks everyone for your input

The different ways of doing things all provide the desired result.

Thanks again