Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2010 VBA - Measuring a range of cells

Posted on 2012-08-16
3
Medium Priority
?
263 Views
Last Modified: 2012-08-18
I have a group of cells (see attached XLS or view png graphic)  and would like vba code to determine the size of the area (i.e. 4 columns, 28 rows).
errregion-measurement.xlsx
0
Comment
Question by:brothertruffle880
  • 2
3 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38299685
This should do it:

Sub CountRowsAndColumns()
Dim rng As Range
Set rng = ActiveSheet.UsedRange
lRows = rng.Rows.Count
lColumns = rng.Columns.Count
MsgBox "there are " & lRows & " Rows and " & lColumns & " Columns in the used range"
End Sub

Open in new window

0
 
LVL 13

Assisted Solution

by:Ryan
Ryan earned 800 total points
ID: 38300882
Barman, for some reason Excel sometimes considered cleared cells as used.  

If you take the spreadsheet he's provided, paste in your code, and run it, you get 36rows, 4 cols.  Whereas it should say 28rows.


If you always know one of the corners, IE B2, or E2, and the range is continuous (No blank cells) then this code would work.
Sub CountRowsAndColumns()
    Dim BottomRight As Range
    Dim TopLeft As Range

    Set TopLeft = Range("B2")
    Set BottomRight = TopLeft.End(xlDown).End(xlToRight)
    
    lRows = BottomRight.Row - TopLeft.Row + 1
    lColumns = BottomRight.Column - TopLeft.Column + 1
    MsgBox "there are " & lRows & " Rows and " & lColumns & " Columns in the used range"
End Sub

Open in new window

0
 
LVL 24

Accepted Solution

by:
Steve earned 1200 total points
ID: 38301467
OK, this code should do the job no matter what (I hope)

4 functions: then a Sub to test them:

Private Function FirstRow(TheWorksheet As Worksheet) As Long
If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
    FirstRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
End If
End Function
Private Function FirstColumn(TheWorksheet As Worksheet) As Long
If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
    FirstColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
End If
End Function
Private Function LastRow(TheWorksheet As Worksheet) As Long
If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
    LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
End Function
Private Function LastColumn(TheWorksheet As Worksheet) As Long
If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
    LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If
End Function

Sub test()

Dim ws As Worksheet
Set ws = ActiveSheet

lRow = LastRow(ws)
lCol = LastColumn(ws)

fRow = FirstRow(ws)
fCol = FirstColumn(ws)

nColumns = lCol - fCol + 1
nRows = lRow - fRow + 1

Debug.Print nColumns
Debug.Print nRows

End Sub

Open in new window

Last-Cells.xlsm
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question