VBA - Find last used row number within certain columns please

harrycosh

New Member
Joined
Feb 27, 2009
Messages
25
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.

Code:
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
 
Last edited:
Upvote 0
Or perhaps

Code:
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
 
Upvote 0
Or perhaps

Code:
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!!
 
Upvote 0
Hi

A non-looping alternative:

Code:
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
 
Upvote 0
Thanks everyone for your input

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

Thanks again
 
Upvote 0

Forum statistics

Threads
1,196,328
Messages
6,014,670
Members
441,835
Latest member
rthomas268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top