Using VBA to select the last row containing data - Error message Received

[forwork]

Hi,

I've learned a huge amount the last few weeks, a large part of which has come directly from this community. Thanks, in advance!

I'm struggling somewhat with the object-oriented syntax of VBA. The problem I am trying to solve is finding the last row of data in a large table and uploading the contents of each cell in the row to a database.

Here's the code for selecting the last row with data:

Dim WS As Worksheet
Dim SelRange As Range
Dim LastRowNum As Long
Set WS = Worksheets("Daily Prices")
LastRowNum = WS.Range("A" & Rows.Count).End(xlUp).Row
Set SelRange = WS.Range("A" & LastRowNum, "V" & LastRowNum).Select

The error message I receive reads:

Run-time error '424' Object Required

I feel like it's something simple that I don't quite yet grasp about the way VBA handles Range objects versus simple objects like int/long variables. Any insight would be greatly appreciated!

 

[NARAYANK991]

Hi ,

Try this :

Dim WS As Worksheet
Dim SelRange As Range
Dim LastRowNum As Long

Set WS = Worksheets("Daily Prices")
LastRowNum = WS.Range("A" & Rows.Count).End(xlUp).Row

WS.Activate
Range("A" & LastRowNum & ":" & "V" & LastRowNum).Select
Set SelRange = Selection

I have changed the selection to include all the columns from A through V ; if this is not what you want , change the statement replacing the semi-colon by a comma.

Narayan

 

[SirJB7]

Hi, forwork!
Tried yet simply removing the ".Select" from the last line?
Regards!

 

[forwork]

Hi @SirJB7 ,

You've come through again in the clutch! Thanks so much!
I'll have to do some reading on how the basic VBA syntax works. I was getting confused with the requiremet of "selecting" a range versus simply defining a range.

Cheers,

forwork

 

[SirJB7]

Hi, forwork!

In general you don't need to select anything in VBA even if the macro recorder insist in selecting everything. If you avoid doing that then you're code will be cleaner and run faster, since select takes a lot of time and resources.

For example this recorded code:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
  Range("A1").Select
  Selection.Copy
  Range("B1").Select
  ActiveSheet.Paste
End Sub

can be replaced by this (Paste is somehow particular):

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
  Range("A1").Copy
  Range("B1").Select
  ActiveSheet.Paste
End Sub

and even by this (but it´s a bit more advanced):

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Copy Range("B1")
End Sub

Regards!