VBA help in selecting entire rows on a dynamic range

Question

Hi thanks everyone in advance. I have a data set. lets say A3 to Z30. the number of rows and columns varies. also there are blanks in the set. So lets say i want to select the entire section but there's a blank in Z29 and X30 using

Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy

isn't going to work.

But the values in column A is continuous. So i think the first part will work

Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select

now i know this may seem elementry but how the heck do i select all the rows i just highlighted? this needs to be dynamic because as i said the number of columns and rows vary.

Oh and bonus karma and kudos if you can help me to figure out the next part. I need to select the range and paste it immediately after the the last row but the value in the first cell or A31 in this case would need to change and that is being pulled from a list in sheet2

score 5 · Accepted Answer · edited May 23 '17 at 12:24

Use the .EntireRow method.

Here is an example:

Dim report as Worksheet
Set report = Excel.ActiveSheet

report.cells(1,1).EntireRow.Select

If you want to select the cells themselves, you can use the .UsedRange method.

Here is an example:

Dim report As Worksheet
Set report = Excel.ActiveSheet

report.Range(report.Cells(1, 1), report.Cells(1, report.UsedRange.Columns.Count)).Select

EDIT

Here is an example for part II of your question (as requested):

Sub test2()

Dim report As Worksheet
Set report = Excel.ActiveSheet

report.Cells(1, 1).EntireRow.Copy
report.Cells(report.UsedRange.Rows.Count + 1, 1).EntireRow.PasteSpecial xlPasteAll


End Sub

Be sure to note that the .UsedRange method also includes cells that have no values but have been formatted by the user; e.g., if you add bold font (even if you don't add the text itself) to a cell in row 1000, your .UsedRange.Rows.Count will be 1000.

Additionally, you can check my answer in the following link for more guidance. I've been told the notes are very helpful for beginners:

Excel Macro - Paste only non empty cells from one sheet to another (Stack Overflow)

Dude you are awesome. that totally worked. no luck you can help with part II is there? — James Chen, Oct 14 '13 at 21:56
@JamesChen You're welcome, James. Don't forget to mark the answer as 'Correct', and Good luck — Ross Brasseaux, Oct 14 '13 at 22:13
I tried to earlier but you answered so fast i had to wait. the i had a meeting sorry bout that. its marked now good sir — James Chen, Oct 14 '13 at 22:46

score 0 · Answer 2 · answered Oct 14 '13 at 22:04

You might want to look at this, and consider what you could do with Range.CurrentRegion, Range.Resize and Range.Offset, so you might get:

Range("A3").CurrentRegion.Copy

Additionally, there's no need to use Range.Select unless you want a user to see what is happening; instead of (for example) a Range.Select followed by a Selection.Copy() (which copies to the clipboard), you could just use a Range.Copy(Range), which copies direct to the target range.

As to the second part, you could:

Dim CopyRow as Long
CopyRow = Range("A3").CurrentRegion.Rows.Count
Range("A3").CurrentRegion.Copy(Range("A3").CurrentRegion.Offset(CopyRow))
Range("A3").Offset(CopyRow) = x   ' Insert your reference to the Sheet 2 value here

score 0 · Answer 3 · answered May 08 '15 at 01:40

I am aware that this thread is old, however I was looking for help with something similar. I knew my starting cell for my range, but the number of rows and columns were going to be dynamic. Using the following code worked for me:

Range("A2").Select
Range(Selection, Selection.End(xlDown).End(xlToRight)).Select

Hopefully anyone in the future can make use of this simple solution.

VBA help in selecting entire rows on a dynamic range

3 Answers3

EDIT