Macro select blank cells (with formula)

[misiek5510]

HI all,

I have the following macro which works, however is selecting blank cells which is causing problems. The cells are blank however are containing formulas, but since I'm coping them as values only it shouldn't matter?
When I paste it on the sheet "Data" it Pastes with the blank cells, and next time I use this macro it pastes AFTER the blank cells instead of right after the last row with data.

Sub add()

Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Data").Select
Range("Data[[#Headers],[Site]]").Select
Selection.End(xlDown).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
End Sub

 

[misiek5510]

Hi Jason,

It worked partially. It basically copied some data (not the correct range), and pasted it on the wrong column, and every time I run the macro it pastes in the same place as opposed to pasting under previous last row.

 

[Fluff]

Slight typo in Jason's code, try

Sub add()
Dim rFound As Range, rng As Range
Set rFound = ActiveSheet.Cells.Find("?*", Range("A1"), xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
Set rng = Range("B5", rFound)
Sheets("Data").Range("Data[[#Headers],[Site]]").End(xlDown).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End Sub

 

[jasonb75]

Slight typo in Jason's code, try

Thanks @Fluff, not sure why I put that comma there, I'll think of an excuse later

 

[misiek5510]

Yes. Now it almost works. It pastes the data in the right fields and it does paste after the last row with data, however it seems to be coping too many columns (10 and I only use 4). I always need the range B5:E24 to be copied.

 

[jasonb75]

Your original attempt used xlToRight to get the last column, there was no indication that there might be anything beyond the range to copy. As with most questions, it is better if you tell us what you are trying to do clearly rather than simply telling us what you've tried doing and assuming that we can figure it out from that.

Sub add()
Dim rFound As Range, rng As Range
Set rFound = ActiveSheet.Range("E:E").Find("?*", Range("E1"), xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
Set rng = Range("B5", rFound)
Sheets("Data").Range("Data[[#Headers],[Site]]").End(xlDown).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End Sub

 

[misiek5510]

Your original attempt used xlToRight to get the last column, there was no indication that there might be anything beyond the range to copy. As with most questions, it is better if you tell us what you are trying to do clearly rather than simply telling us what you've tried doing and assuming that we can figure it out from that.

Sub add()
Dim rFound As Range, rng As Range
Set rFound = ActiveSheet.Range("E:E").Find("?*", Range("E1"), xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
Set rng = Range("B5", rFound)
Sheets("Data").Range("Data[[#Headers],[Site]]").End(xlDown).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End Sub

That worked great Thank you both. Apologies, I specified my range in the 2nd reply, however next time I'll try to be more precise

 

[Fluff]

Glad we could help & thanks for the feedback.