Numrows function is not working in Excel 2010 VBA

misterno

Board Regular
Joined
Mar 16, 2009
Messages
77
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
numRows isn't a function, it's a variable and this line of code is trying to assign a value to it.
Code:
numRows = Range(rgQuotes, rgQuotes.End(xlDown)).Rows.Count

What error do you get on that line?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Mar 16, 2009
Messages
77

ADVERTISEMENT

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

Board Regular
Joined
Mar 16, 2009
Messages
77
numRows isn't a function, it's a variable and this line of code is trying to assign a value to it.
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

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
Top