VB Question - Qet Number of used Rows and Columns

campbbri

New Member
Joined
Oct 27, 2004
Messages
36
Hi everyone,

In a VB macro I am running a need to add code that finds the number of rows and columns in a sheet that contains data.

For example, lets say a particular sheet values in cells A1, A2, A7, and D5.

I would like to read in that there are 7 rows being used (because of cell A7), and 4 columns (because of cell D5).

Because there could be gaps between rows and columns, I can't figure out a way to get this data without checking all columns.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi

Code:
Sub Test()
Dim LastRow As Long, LastColumn As Long
LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LastColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
End Sub
 
Upvote 0
Errors

Thank you so much, it works perfectly but I have one problem left. This code is run against many worksheets, which occasionally are blank. Unfortunately these blank sheets cause an error.

Is there a way to modify your code below to return 1 row and 1 column in this case instead of returning an error?

Thanks again!
 
Upvote 0
Code:
Sub Test()
Dim LastRow As Long, LastColumn As Long
If Application.CountA(Cells) = 0 Then
    LastRow = 1
    LastColumn = 1
Else
    LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LastColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
End If
End Sub
 
Upvote 0
To answer your question(s) directly, I would've done exactly as Richard and Boller did.
However, (depending on just what you're doing this for I guess) returning a 1 row, 1 column
result for an empty sheet could be misleading. (Is it after all inaccurate, yes?)

You could modify the code to return zeros instead of ones or you could just use an
On Error Resume Next statement to ignore the blank sheet(s)

(Just a thought.) Hope it helps.
 
Upvote 0
Perhaps

Code:
Sub Test()
Dim LastRow As Long, LastColumn As Long, N As Long
N = Application.CountA(ActiveSheet.Cells)
If N > 0 Then
    LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LastColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Else
    LastRow = 0
    LastColumn = 0
End If
MsgBox LastRow & " Rows" & Chr(10) & LastColumn & " columns"
End Sub
 
Upvote 0
Actually, this is more what I had in mind:
Code:
Dim LastRow&, LastColumn&
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LastColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
MsgBox LastRow & " Rows" & vbLf & LastColumn & " columns"

The variables are by default zero until assigned otherwise.
When it tries to assign them as otherwise (on a blank sheet) it results in an error, and with
the Resume Next line they're prevented from being assigned as anything so they just remain
zero. :wink:
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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