How to count rows and columns in a used range

[exceltime]

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.

 

[Yongle]

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)

Name	HdrB	HdrC	HdrD	HdrE	HdrF
name01	b1	c1	d1	e1	f1
name02	b2	c2	d2	e2	f2
name03	b3	c3	d3	e3	f3
name04	b4	c4	d4	e4	f4
name05	b5	c5	d5	e5	f5
name06	b6	c6	d6	e6

<tbody>
</tbody>

and run this code with that sheet active

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

 

[exceltime]

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

 

[Yongle]

What is the problem?
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?

 

[exceltime]

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

 

[exceltime]

here is the 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.