1

I have a piece of code which selects the entire row from my table based on the last data in column K. E.g. if I have rows 5 to 10 populated, it will select row 10.

How do I get the code to select multiple rows from row 5 all the way to the last row as defined below?

Thanks

Sub selectlastrow()

Dim lastrow As Long
Dim report As Worksheet
Set report = Excel.ActiveSheet

Sheets("Risks").Select

lastrow = Range("K5:K48").End(xlDown).Row

report.Cells(lastrow, 2).EntireRow.Select

End Sub

To follow up:

I'm stuck on how to structure a piece of code that:

  1. Loops through all worksheets that begin with the number 673: (e.g. 673:green, 673:blue)

  2. Selects the data in these worksheets from row 5 up until the last row with data - code that works for this is

    With report
.Range(.Cells(5, "K"), .Cells(.Rows.Count, "K").End(xlUp)).EntireRow.Select
End With
  3. Select the "Colours" worksheet
  4. Paste the rows at the next available blank row. There could be up to 40/50 worksheets which will have data pasted into the "Colours" worksheet so I need the data added to the next available line.

Thank you in advance.

Vince
  • 33
  • 1
  • 2
  • 6
  • fwiw, when you say *' the last data in column K'*, it is better to look from the bottom up then from the top down. –  May 06 '16 at 14:36
  • 1
    FYI - It's best practice to [avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). I recommend reading through that, to learn how to work directly with your data. – BruceWayne May 06 '16 at 14:37

4 Answers4

2

You have multiple ways to select a range of rows. Easiest based on available data you have:

report.Range("K5").resize(lastrow - 4).EntireRow.Select
yk11
  • 768
  • 4
  • 9
2

Use a cell reference that combines both the Range object and Range.Cells property.

with report
     .Range(.Cells(5, "K"), .Cells(.rows.count, "K").End(xlUp)).EntireRow.Select
end with
1
Report.Range("B5:B" & lastrow).EntireRow.Select
user3598756
  • 28,893
  • 4
  • 18
  • 28
0

Depending on what you're actually doing with your code, just replace the expression inside of rowRange.

Dim rowRange As Range
Set rowRange = Range("a1:A8").Rows.EntireRow
rowRange.Select

Voila!

Zombro
  • 311
  • 1
  • 9