VB Question - Qet Number of used Rows and Columns

[campbbri]

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.

 

[Richard Schollar]

Hi

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

 

[campbbri]

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!

 

[Boller]

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

 

[HalfAce]

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.

 

[VoG]

Perhaps

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

 

[HalfAce]

Actually, this is more what I had in mind:

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: