Numrows function is not working in Excel 2010 VBA

[misterno]

It stops at numrows line

Please help

Dim ws As Worksheet
Dim rgQuotes As Range
Dim rgPasteTo As Range
Dim rgTable As Range
Dim rgBid As Range
Dim nRows As Integer
' Initialize
'
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
' Set objects
Set ws = ActiveWorkbook.Sheets("Filter")
Set rgPasteTo = ws.Range("PasteTo")

' Clean up
ActiveWorkbook.Sheets("Dump").Activate
Set rgQuotes = ActiveSheet.Range("FilteredQuotes")

If IsEmpty(rgQuotes) Then ' Error handling
Range(rgQuotes, rgQuotes.Offset(1, 0)).Value = "Quotes will come here..."
End If

numRows = Range(rgQuotes, rgQuotes.End(xlDown)).Rows.Count
Set rgTable = rgQuotes.Resize(numRows, 6)
rgTable.ClearContents
Set rgTable = Nothing

 

[misterno]

can someone please help me?

 

[Norie]

numRows isn't a function, it's a variable and this line of code is trying to assign a value to it.

numRows = Range(rgQuotes, rgQuotes.End(xlDown)).Rows.Count

What error do you get on that line?

 

[Rick Rothstein]

It stops at numrows line

Please help

Dim ws As Worksheet
Dim rgQuotes As Range
Dim rgPasteTo As Range
Dim rgTable As Range
Dim rgBid As Range
Dim nRows As Integer
' Initialize
'
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
' Set objects
Set ws = ActiveWorkbook.Sheets("Filter")
Set rgPasteTo = ws.Range("PasteTo")

' Clean up
ActiveWorkbook.Sheets("Dump").Activate
Set rgQuotes = ActiveSheet.Range("FilteredQuotes")

If IsEmpty(rgQuotes) Then ' Error handling
Range(rgQuotes, rgQuotes.Offset(1, 0)).Value = "Quotes will come here..."
End If

numRows = Range(rgQuotes, rgQuotes.End(xlDown)).Rows.Count
Set rgTable = rgQuotes.Resize(numRows, 6)
rgTable.ClearContents
Set rgTable = Nothing

What happens if you replace "Integer" with "Long" in the code line I highlighted in red?

 

[misterno]

What happens if you replace "Integer" with "Long" in the code line I highlighted in red?

I tried that but it did not work

So it stops at numRows = Range(rgQuotes, rgQuotes.End(xlDown)).Rows.Count

Please help

 

[misterno]

numRows isn't a function, it's a variable and this line of code is trying to assign a value to it.

numRows = Range(rgQuotes, rgQuotes.End(xlDown)).Rows.Count

What error do you get on that line?

This is the error;

Compile error

Can't find project or library
--------------------

Sorry I forgot to add, I just converted from 2007 excel to 2010

I think 2010 VBA does not recognize some 2007 vba

 

[Norie]

Can you post all the code for this sub/function?

Also, take a look under Tools>References...

If you see anything there marked as MISSING note it's name then uncheck it and try the code again.

 

[misterno]

Thanks it is working now