Visual Basic: Get row count of a sheet in Excel

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").
 
B

Bob Phillips

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
 
L

Lars-Åke Aspelin

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
 
L

Lars-Åke Aspelin

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
 
C

Curious

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?
 
L

Lars-Åke Aspelin

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
 
C

Curious

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!
 
L

Lars-Åke Aspelin

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
 
C

Curious

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.
 
L

Lars-Åke Aspelin

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Get the row count of different areas in the Excel 2
Need Help with a VBA subroutine 0
Excel Visual Basic to get current row number 1
Word macro that can call cells out of Excel for a find and replace 0
add hyperlink using sheet names and cells(i,j) 3
Copy unformatted value of cell via macro 0
Help changing the sheet name 3
paste row into Sheet2 in next row 1

Top