Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Select Range, less last row

  Asked By: Karina    Date: Mar 11    Category: MS Office    Views: 4815
  

I want to select and copy a range of variable size. I know the the first
row of the range is from "G3:M3" However, I don't know how many rows deep
the range will be. Sometimes the Range may be ("G3:M27") or ("G3:M15").

Additionally, I want to select everything EXCEPT the last row in the range.
Also, there is other data below the first range of data that I don't want, so
using Range("G65536").End(xlUp) won't work either.

I used the following code and I keep getting Error '91' Object variable or
With block variable not set.

I think it's close to doing what I want it to do?!

Sub lastrow()

Dim lastrow As Range
Sheets("sheet1").Select
lastrow = Range("g3:m3").End(xlDown).Row - 1
Range("g3" & lastrow).Select

End Sub

Suggestions?

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Jake Evans     Answered On: Mar 11


Try:

Sub last_row()

'Dim lastrow As Range
Sheets("sheet1").Select
lastrow = Range("g3:m3").End(xlDown).Row - 1
Range("G3:M" & lastrow).Select

End Sub


Changes made are:
1.The name of the sub; you named the sub the same as a variable  name.
2.The commenting out of the Dim line; you defined lastrow as a range
then you try 2 lines later to assign a number to it. You don't need
it.
3.Changed
Range("g3" & lastrow).Select
to
Range("G3:M" & lastrow).Select


You could tweak it a bit more by changing the line:
lastrow = Range("g3:m3").End(xlDown).Row - 1
to
lastrow = Range("g3").End(xlDown).Row - 1
since xl won't look in multiple columns for the last cell, only one,
the active cell column. Replace the G of G3 with the column letter of
the column you want to find the last entry of.

 
Didn't find what you were looking for? Find more on Select Range, less last row Or get search suggestion and latest updates.




Tagged: