How to count rows and columns in a used range

E

exceltime

New Member
Joined
Aug 19, 2014
Messages
29
Hi experts,
I have two quite simple questions:
what is the statement for finding the number of rows with at least one field filled. Similar question for columns, but this time first I want to know to know the column label together with its number as integer. Secondly using these two numbers I want to define a range in which I will do the rest of the project. Here is code that I tried. But it does not work:
Code:
IMAXC = ActiveSheet.Cells(Rows.Count, "F").UsedRange.Rows.Countset
IMAXR = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).RowI know the code looks stupid and not complete at all. But I got error messages all the time. Just confused. Please help.

 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Perhaps this will help

First of all paste this data into a worksheet , starting in A1 (contains several empty rows, the last row is row 13)

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Name[/TD]
[TD="class: xl63, width: 64"]HdrB[/TD]
[TD="class: xl63, width: 64"]HdrC[/TD]
[TD="class: xl63, width: 64"]HdrD[/TD]
[TD="class: xl63, width: 64"]HdrE[/TD]
[TD="class: xl63, width: 64"]HdrF[/TD]
[/TR]
[TR]
[TD="class: xl63"]name01[/TD]
[TD="class: xl63"]b1[/TD]
[TD="class: xl63"]c1[/TD]
[TD="class: xl63"]d1[/TD]
[TD="class: xl63"]e1[/TD]
[TD="class: xl63"]f1[/TD]
[/TR]
[TR]
[TD="class: xl63"]name02[/TD]
[TD="class: xl63"]b2[/TD]
[TD="class: xl63"]c2[/TD]
[TD="class: xl63"]d2[/TD]
[TD="class: xl63"]e2[/TD]
[TD="class: xl63"]f2[/TD]
[/TR]
[TR]
[TD="class: xl63"]name03[/TD]
[TD="class: xl63"]b3[/TD]
[TD="class: xl63"]c3[/TD]
[TD="class: xl63"]d3[/TD]
[TD="class: xl63"]e3[/TD]
[TD="class: xl63"]f3[/TD]
[/TR]
[TR]
[TD="class: xl63"]name04[/TD]
[TD="class: xl63"]b4[/TD]
[TD="class: xl63"]c4[/TD]
[TD="class: xl63"]d4[/TD]
[TD="class: xl63"]e4[/TD]
[TD="class: xl63"]f4[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]name05[/TD]
[TD="class: xl63"]b5[/TD]
[TD="class: xl63"]c5[/TD]
[TD="class: xl63"]d5[/TD]
[TD="class: xl63"]e5[/TD]
[TD="class: xl63"]f5[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]name06[/TD]
[TD="class: xl63"]b6[/TD]
[TD="class: xl63"]c6[/TD]
[TD="class: xl63"]d6[/TD]
[TD="class: xl63"]e6[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

and run this code with that sheet active
Code: 
Sub SomeStats()
    Dim rng As Range, fr As Long, lr As Long, rCount As Long, addr As String
    With ActiveSheet
        'using CurrentRegion (contiguous range)
        Set rng = .Range("A1").CurrentRegion
        addr = rng.Address(0, 0)
        lr = rng(rng.Rows.Count, 1).Row
        fr = rng(1, 1).Row
        rCount = lr - fr + 1
        rng.Select
    MsgBox addr & vbTab & "Rowcount " & rCount, , "CurrentRegion"
        'if you want to ignore headers
        Set rng = .Range("A1").CurrentRegion
        Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
        addr = rng.Address(0, 0)
        lr = rng(rng.Rows.Count, 1).Row
        fr = rng(1, 1).Row
        rCount = lr - fr + 1
        rng.Select
    MsgBox addr & vbTab & "Rowcount " & rCount, , "CurrentRegion - excl headers"
        'UsedRange
        Set rng = .UsedRange
        addr = rng.Address(0, 0)
        lr = rng(rng.Rows.Count, 1).Row
        fr = rng(1, 1).Row
        rCount = lr - fr + 1
        rng.Select
    MsgBox addr & vbTab & "Rowcount " & rCount, , "UsedRange"
        'using a column to determine
        lr = .Range("F" & Rows.Count).End(xlUp).Row
        Set rng = .Range("A2:H" & lr)
        addr = rng.Address(0, 0)
        fr = rng(1, 1).Row
        rCount = lr - fr + 1
        rng.Select
    MsgBox addr & vbTab & "Rowcount " & rCount, , "Using a column"
    End With
End Sub
 
Last edited:
Upvote 0
HI, I have used your code. bur it gives errors such as object not found, with without end and so on. I am frustrated. Is there no other way to help. Thanks
 
Upvote 0
What is the problem? :confused:
The code I gave you works perfectly and does not error when used with the data provided

All you need do is
- paste the data provided into new worksheet
- paste the code into a module
- ensure the sheet with the data is active
- run the code

Are you saying that you have been unable to adapt the code to match your own data?
 
Upvote 0
Yes indeed I have difficulties. In fact all I need to know is IMAX the number of records in the activesheet,which is occupied up to thousands called on colF. I call the function twice. Based on that I have defined
two parameters namely rCount and rng. When the program is called these are empty. Then
IMAX = rCount
sRanse = rng and I have commented out the unnecesary

 
Upvote 0
here is the code:
Code: 
Option ExplicitSub prepareData()
On Error GoTo Error_sub
Determine rCount, rng
sRange = rng
sRange.Activate
IMAX = rCount
'IMAX = wb.Sheets(sName).UsedRange.Rows.CountSet
'IMAXR = wb.Sheets("sname").UsedRange.Columns.Count
'IMAXC = Workbook.Sheets("sname").UsedRange.Rows.Count
'---------------------------------------------------------------------------------------


' Textval is string to be searched
  
    TextVal = InputBox("Enter text to search", , tName)
'
 
    If tName = "" Then
        MsgBox "No results to search for cancelled"
        GoTo EX_sub
    End If
    
    sRange.Activate
    I = rCount
    Sheets(tName).Activate
  
    JMAX = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
  
    Sheets(tName).Activate '------------------------------------------------------------------------------
'   get input and define Sheets(tName) sheet
'------------------------------------------------------------------------------
' Textval is string to be searched
  
    TextVal = InputBox("Enter text to search", , tName)
'
 
    If tName = "" Then
        MsgBox "No results to search for cancelled"
        GoTo EX_sub
    End If
    
    sRange.Activate
    I = rCount
    Sheets(tName).Activate
  
    JMAX = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
  
    Sheets(tName).Activate
If JMAX = 0 Then
    Sheets(tName).Cells(1, ColA) = "Value Date"
    Sheets(tName).Cells(1, ColB) = "Entry"
    Sheets(tName).Cells(1, ColC) = "Amount"
    Sheets(tName).Cells(1, ColD) = "Search word"
    Sheets(tName).Cells(1, ColE) = "Counterparty"
    Sheets(tName).Cells(1, ColF) = "Parameters"
    Sheets(tName).Cells(2, ColF) = "Upper limit"
    Sheets(tName).Cells(3, ColF) = "Lower limit"
    Sheets(tName).Cells(4, ColF) = "TOTAL"
    MsgBox "Output Sheet is established"
Else
    If Sheets(tName).Cells(1, ColA) = "Value Date" And _
        Sheets(tName).Cells(1, ColB) = "Entry" And _
        Sheets(tName).Cells(1, ColA) = "Value Date" And _
        Sheets(tName).Cells(1, ColB) = "Entry" And _
        Sheets(tName).Cells(1, ColC) = "Amount" And _
        Sheets(tName).Cells(1, ColD) = "Search word" And _
        Sheets(tName).Cells(1, ColE) = "Counterparty" And _
        Sheets(tName).Cells(1, ColF) = "Parameters" And _
        Sheets(tName).Cells(2, ColF) = "Upper limit" And _
        Sheets(tName).Cells(1, ColC) = "Amount" And _
        Sheets(tName).Cells(1, ColD) = "Search word" And _
        Sheets(tName).Cells(1, ColE) = "Counterparty" And _
        Sheets(tName).Cells(3, ColF) = "Lower limit" And _
        Sheets(tName).Cells(4, ColF) = "TOTAL" Then
        MsgBox "Output Sheet is correct"
    End If
End If
Error_sub:
    MsgBox Err.Number & Err.Description & Chr(13) & " Wrong input"
EX_sub:
End Sub
Your function is renamed determine.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA count rows
Replies
3
Views
269
Excelogist
E
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
808
Peter_SSs
Peter_SSs
Y
  • Question Question
How to count number of consecutive wet days in the a rainfall table?
2
Replies
19
Views
841
felixstraube
F
P
  • Question Question
Countif using totals of two named columns
2 3
Replies
20
Views
581
Cubist
Cubist
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
305
Moley84
M

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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