Visual Basic: Get row count of a sheet in Excel

  • Thread starter Thread starter Curious
  • Start date Start date
C

Curious

I have three sheets in the Excel, by default, they're called Sheet1,
Sheet2, and Sheet3.

I renamed the sheets to "MarketData", "LongTermLimits", and
"ShortTermLimits" respectively.

I want to create a VB (macro?) inside an Excel file as below:

Sub MatchShortTermLimits()
Sheet2.UsedRange.Rows.Count
End Sub

It gives me an error, "Compile error: Invalid use of property"

I changed the code to:

Sub MatchShortTermLimits()
LongTermLimits.UsedRange.Rows.Count
End Sub

It then complains "Object required"

Any advice on how to get this fixed? I only want to get the row count
of Sheet2 (renamed to "LongTermLimits").
 
You have to do something with that value

Sub MatchShortTermLimits()
myVal = Sheet2.UsedRange.Rows.Count
End Sub

or

Sub MatchShortTermLimits()
MsgBox Sheet2.UsedRange.Rows.Count
End Sub
 
I have three sheets in the Excel, by default, they're called Sheet1,
Sheet2, and Sheet3.

I renamed the sheets to "MarketData", "LongTermLimits", and
"ShortTermLimits" respectively.

I want to create a VB (macro?) inside an Excel file as below:

Sub MatchShortTermLimits()
Sheet2.UsedRange.Rows.Count
End Sub

It gives me an error, "Compile error: Invalid use of property"

I changed the code to:

Sub MatchShortTermLimits()
LongTermLimits.UsedRange.Rows.Count
End Sub

It then complains "Object required"

Any advice on how to get this fixed? I only want to get the row count
of Sheet2 (renamed to "LongTermLimits").
Click to expand...


Try this:

Sub MatchShortTermLimits()
Worksheets("LongTermLimits").UsedRange.Rows.Count
End Sub

Hope this helps / Lars-Åke
 
Try this:

Sub MatchShortTermLimits()
Worksheets("LongTermLimits").UsedRange.Rows.Count
End Sub

Hope this helps / Lars-Åke
Click to expand...

Ooops, should be something like this

Sub MatchShortTermLimits()
myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count
End Sub

Lars-Åke
 
Thanks to all who provided their help! Now I have the following script
and got an "application-defined or object-defined" error at the line
marked below:

Sub MatchShortTermLimits()

myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count

Sheets("LongTermLimits").Select
Dim r As Range, cell As Range
With ActiveSheet
Set r = .Range(.Cells(2, "D"), .Cells(myrowcount + 1, "D").End
(xlToLeft))
End With

i = 2
Do While i <= myrowcount + 1
If ActiveSheet.Cells(E, i) = "S" Then ' "application-
defined or object-defined" error
ActiveSheet.Cells(J, i).Value = Max(ActiveSheet.Cells(D,
i), ActiveSheet.Cells(H, i))
Else
If ActiveSheet.Cells(E, i) = "B" Then
ActiveSheet.Cells(J, i).Value = Min(ActiveSheet.Cells
(D, i), ActiveSheet.Cells(H, i))
End If
End If
i = i + 1
Loop

ActiveSheet.Save

End Sub

It seems that "ActiveSheet.Cells(E, i)" causes the problem. Any input?
 
Thanks to all who provided their help! Now I have the following script
and got an "application-defined or object-defined" error at the line
marked below:

Sub MatchShortTermLimits()

myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count

Sheets("LongTermLimits").Select
Dim r As Range, cell As Range
With ActiveSheet
Set r = .Range(.Cells(2, "D"), .Cells(myrowcount + 1, "D").End
(xlToLeft))
End With

i = 2
Do While i <= myrowcount + 1
If ActiveSheet.Cells(E, i) = "S" Then ' "application-
defined or object-defined" error
ActiveSheet.Cells(J, i).Value = Max(ActiveSheet.Cells(D,
i), ActiveSheet.Cells(H, i))
Else
If ActiveSheet.Cells(E, i) = "B" Then
ActiveSheet.Cells(J, i).Value = Min(ActiveSheet.Cells
(D, i), ActiveSheet.Cells(H, i))
End If
End If
i = i + 1
Loop

ActiveSheet.Save

End Sub

It seems that "ActiveSheet.Cells(E, i)" causes the problem. Any input?
Click to expand...


Change
Cells(E,i) to Cells(i,"E")
Cells(J,i) to Cells(i,"J")
Cells(D,i) to Cells(i,"D")
Cells(H,i) to Cells(i,"H")
etc

Hope this helps / Lars-Åke
 
Thanks Lars-Åke! It works!

Now I've got another error, "Type mismatch" at the line marked below:

Sub MatchShortTermLimits()

myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count

Sheets("LongTermLimits").Select
Dim r As Range, cell As Range
With ActiveSheet
Set r = .Range(.Cells(2, "D"), .Cells(myrowcount + 1, "D").End
(xlToLeft))
End With

i = 2
Do While i <= myrowcount + 1
If ActiveSheet.Cells("E", i).Value = "S" Then ' "Type
mismatch" error here
ActiveSheet.Cells("J", i).Value = Max(ActiveSheet.Cells
("D", i).Value, ActiveSheet.Cells("H", i).Value)
Else
If ActiveSheet.Cells("E", i).Value = "B" Then
ActiveSheet.Cells("J", i).Value = Min(ActiveSheet.Cells
("D", i).Value, ActiveSheet.Cells("H", i).Value)
End If
End If
i = i + 1
Loop

ActiveSheet.Save

End Sub

Anyone could again tell me what is wrong? Please help me!
 
Thanks Lars-Åke! It works!

Now I've got another error, "Type mismatch" at the line marked below:

Sub MatchShortTermLimits()

myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count

Sheets("LongTermLimits").Select
Dim r As Range, cell As Range
With ActiveSheet
Set r = .Range(.Cells(2, "D"), .Cells(myrowcount + 1, "D").End
(xlToLeft))
End With

i = 2
Do While i <= myrowcount + 1
If ActiveSheet.Cells("E", i).Value = "S" Then ' "Type
mismatch" error here
ActiveSheet.Cells("J", i).Value = Max(ActiveSheet.Cells
("D", i).Value, ActiveSheet.Cells("H", i).Value)
Else
If ActiveSheet.Cells("E", i).Value = "B" Then
ActiveSheet.Cells("J", i).Value = Min(ActiveSheet.Cells
("D", i).Value, ActiveSheet.Cells("H", i).Value)
End If
End If
i = i + 1
Loop

ActiveSheet.Save

End Sub

Anyone could again tell me what is wrong? Please help me!
Click to expand...


You changed Cells(E,i) to Cells("E",i) but I think you should change
it to Cells(i,"E").
And the same for the other, similar, calls to Cells.

Hope this helps / Lars-Åke
 
Thanks Lars-Åke! It works.

It works fine until it loops to a row with column H whose value is "#N/
A", where I get a "Type mismatch" error at runtime. I thought I had
filered out these rows by using an IF statement ("If ActiveSheet.Cells
(i, "H").Value <> "#N/A" Then" ).

Anyway, my code is below:

Sub MatchShortTermLimits()

myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count

Sheets("LongTermLimits").Select
Dim r As Range, cell As Range
With ActiveSheet
Set r = .Range(.Cells(2, "D"), .Cells(myrowcount + 1, "D").End
(xlToLeft))
End With

i = 2
Do While i <= myrowcount
If ActiveSheet.Cells(i, "E").Value = "S" Then
If ActiveSheet.Cells(i, "H").Value <> "#N/A" Then
ActiveSheet.Cells(i, "J").Value = Max(ActiveSheet.Cells
(i, "D").Value, ActiveSheet.Cells(i, "H").Value)
End If
Else
If ActiveSheet.Cells(i, "E").Value = "B" Then
If ActiveSheet.Cells(i, "H").Value <> "#N/A" Then
ActiveSheet.Cells(i, "J").Value = Min
(ActiveSheet.Cells(i, "D").Value, ActiveSheet.Cells(i, "H").Value)
End If
End If
End If
i = i + 1
Loop

ActiveSheet.Save

End Sub

How to resolve the issue about a column having both numeric and text
value? Also how can I know at which line I get the "Type mismatch
error"? Please help me.
 
Thanks Lars-Åke! It works.

It works fine until it loops to a row with column H whose value is "#N/
A", where I get a "Type mismatch" error at runtime. I thought I had
filered out these rows by using an IF statement ("If ActiveSheet.Cells
(i, "H").Value <> "#N/A" Then" ).

Anyway, my code is below:

Sub MatchShortTermLimits()

myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count

Sheets("LongTermLimits").Select
Dim r As Range, cell As Range
With ActiveSheet
Set r = .Range(.Cells(2, "D"), .Cells(myrowcount + 1, "D").End
(xlToLeft))
End With

i = 2
Do While i <= myrowcount
If ActiveSheet.Cells(i, "E").Value = "S" Then
If ActiveSheet.Cells(i, "H").Value <> "#N/A" Then
ActiveSheet.Cells(i, "J").Value = Max(ActiveSheet.Cells
(i, "D").Value, ActiveSheet.Cells(i, "H").Value)
End If
Else
If ActiveSheet.Cells(i, "E").Value = "B" Then
If ActiveSheet.Cells(i, "H").Value <> "#N/A" Then
ActiveSheet.Cells(i, "J").Value = Min
(ActiveSheet.Cells(i, "D").Value, ActiveSheet.Cells(i, "H").Value)
End If
End If
End If
i = i + 1
Loop

ActiveSheet.Save

End Sub

How to resolve the issue about a column having both numeric and text
value? Also how can I know at which line I get the "Type mismatch
error"? Please help me.
Click to expand...

Change

If ActiveSheet.Cells(i,"H").Value <> "#N/A" Then

to

If Not IsError(ActiveSheet.Cells(1, "H").Value) Then

You can use the debugger to print the value of variable i when your
macro stops due to an error.
(Press CTRL+G and type ?i in the Immediate window.)

Hope this helps / Lars-Åke
 
Back
Top