Selecting a Certain number of rows in a Spreadsheet

[Grothgar]

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

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

 

[VoG]

Try

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

 

[thankyou]

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]

Try

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

Well... um... that was easier than I thought
Thanks for your help, but could I ask another question?

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]

Try

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

 

[thankyou]

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]

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.