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

 

[jasonb75]

Can there be intermittent blanks in the data? The best way is you use a find command on the data range working backwards with "*?" as criteria.

The problem with this is that if there are blanks in the bottom right corner of the actual data range then you could miss the last row / column if it is not done correctly.

 

[Fluff]

How about

Sub misiek()
   Dim Rng As Range
   
   Set Rng = Range("B5", Range("B5").End(xlDown))
   Set Rng = Range(Rng, Rng.End(xlToRight))
   With Sheets("Data").Range("Data[[#Headers],[Site]]").End(xlDown)
      .Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value
   End With
End Sub

 

[jasonb75]

Don't think that's gonna fix the problem @Fluff, it's still gonna pick up and transfer the extra blanks at the bottom. I was thinking more like this, although it probably needs some refinement.

Sub add()
Dim rFound As Range
Set rFound = ActiveSheet.Cells.Find("?*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
Sheets("Data").Range("Data[[#Headers],[Site]]").Value = Range("B5", fRound).Value
End Sub

 

[Fluff]

But the blank cells will be empty rather than containing a null string, so all should be ok.

 

[jasonb75]

the blank cells will be empty

I'm showing my lack of vba skill again, I thought that it would still see the null strings in the empty rows as values though. I just ran your code on a quick test to satisfy my curiosity and the one thing that I notice is that it still adds empty rows at the bottom of the table equal to the number of blank rows in the source data even though there is nothing in them. Think it would be tidier if the blanks were snipped at source so that the table ends with the last row of actual data, but that's probably just me having an ocd moment.

 

[misiek5510]

How about

Sub misiek()
   Dim Rng As Range
  
   Set Rng = Range("B5", Range("B5").End(xlDown))
   Set Rng = Range(Rng, Rng.End(xlToRight))
   With Sheets("Data").Range("Data[[#Headers],[Site]]").End(xlDown)
      .Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value
   End With
End Sub

Hi. Thank you for replying. I just got back to work to try both codes and the code from Fluff does not fail, but absolutely nothing happens for some reason? and the code from jasonb fails.

I've amended the code to as follows, but it still does not work. It selects the range and it pastes it in the table but always replaces the last row and sometimes it pastes over the blanks and sometimes it doesn't..


Sub test2()
Range("B5:E24").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

 

[jasonb75]

and the code from jasonb fails.

'fails' is a bit of an ambiguous term, could you be more specific please.
You will note that I did say in my post,

I was thinking more like this, although it probably needs some refinement.

the code provided was only a quick untested theory in a side discussion with fluff, it wasn't intended to be a final working method.

In theory this should work, but if you currently have blanks at the bottom of the Data table from previous attempts then you will need to clear them first.

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

 

[misiek5510]

Thank you for your reply. Apologies, the error says: Run-time error 1004: Method Range of object Global Failed.

 

[jasonb75]

See if this works,

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