Selecting a Certain number of rows in a Spreadsheet

Grothgar

Board Regular
Joined
Jul 25, 2011
Messages
62
Hi All;

The spreadsheet that I am putting together, has 1000 rows of data in each Tab, but not all those 1000 rows are completed (have full rows of data)

What I am trying to do, is to select a cell that I know will have data in it all the way to the bottom of this set of data (but not the 1000 rows), and then select all the rows from the start to that row.

I will then get the Macro to copy this and paste into another worksheet.
At the moment I have this
Code:
Dim lLR As Long
lLR = Range("R5").End(xlDown).Row
Range("R" & Rows.Count & ":R" & lLR).EntireRow.Copy

However, this copies everything from the last row downwards, rather than everything from the first row (row 5) to the end row... what am I doing wrong?

In summary... I want to copy a certain amount of data, but cannot use End(x1down) on its own because that will select all 1000 rows.

Thanks
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Range("R5:R" & lLR).EntireRow.Copy
 

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
2 Easy Questions: 1) where do I start new thread; 2) how to default to 1 sheet

I've used this site in past, but golly, i can't find the new post sergeant! (sorry to arrive here). Can you provide a link? Also, every time I open a new excel, there's 3 sheets. I only want 1 every time. But if I change that under Tools >Options > General ....to 1sheet, it doesn't ever make a difference. Thanks for someone's help.
 

Grothgar

Board Regular
Joined
Jul 25, 2011
Messages
62
Try

Code:
Range("R5:R" & lLR).EntireRow.Copy

Well... um... that was easier than I thought :unsure:
Thanks for your help, but could I ask another question?
Code:
Range("B3").Select
Selection.End(xlDown).Offset(1, 0).Select

The above will select one specific cell that is at the bottom of Row B3, and then one less. However, I need it to do that, but then select the Entire Row rather than just one cell.

It does not seem to matter where I put Row, it keeps coming up as an Debug Error... I am sure it is simple, but driving me to distraction.

Thanks
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try

Code:
Range("B3").Select
Selection.End(xlDown).Offset(1, 0).EntireRow.Select
 

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
Grothgar, I received an email that you responded to my questions. It doesn't look like it was a response to my questions. Please advise, thanks.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Grothgar, I received an email that you responded to my questions. It doesn't look like it was a response to my questions. Please advise, thanks.


Why are you posting in this thread? You should start a new thread for your question.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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