Copy and past non blank cells

[jtodd]

What I need is to copy and past data only - Skip blanks
This works if i use paste special but cannot make it work using vba see below.

Sheets("Tracker2").Select
Range("D3:h431").Select
Selection.Copy
Sheets("Tracker").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Help

 

[igold]

Hi jtodd,

This is really a work around as far as I am concerned but what the heck. Try it and see if it does what you want.

Please remember to run the code on a backup copy of your data.

Sub DelBlanks()

Dim wsT2 As Worksheet: Set wsT2 = Worksheets("Tracker2")
Dim wsT As Worksheet: Set wsT = Worksheets("Tracker")

wsT2.Range("D3:h431").Copy
wsT.Range("C3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
wsT.Range("C3:H431").SpecialCells(xlCellTypeBlanks).Delete

End Sub

HTH

igold

 

[jtodd]

Hi jtodd,


This is really a work around as far as I am concerned but what the heck. Try it and see if it does what you want.

Please remember to run the code on a backup copy of your data.

Sub DelBlanks()

Dim wsT2 As Worksheet: Set wsT2 = Worksheets("Tracker2")
Dim wsT As Worksheet: Set wsT = Worksheets("Tracker")

wsT2.Range("D3:h431").Copy
wsT.Range("C3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
wsT.Range("C3:H431").SpecialCells(xlCellTypeBlanks).Delete

End Sub

HTH

igold

I'm good with a workaround as long as it works , and it does.

Thanks for your time

 

[igold]

Great, glad I could help. Thanks for the feedback.


igold

 

[jtodd]

I'm good with a workaround as long as it works , and it does.

Thanks for your time

Sorry but I have come across a problem , if the cell is has a formula in it but no data -blank- it coppies the blank cell , is there a way round this ??

 

[igold]

Try this... Again not the perfect answer...

Remember, test on backup copy of data.

Sub DelBlanks()

Dim wsT2 As Worksheet: Set wsT2 = Worksheets("Tracker2")
Dim wsT As Worksheet: Set wsT = Worksheets("Tracker")

wsT2.Range("d3:h431").Copy
wsT.Range("C3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
wsT.Range("C3:H431").SpecialCells(xlFormulas).Delete shift:=xlShiftUp
wsT.Range("C3:H431").SpecialCells(xlCellTypeBlanks).Delete
End Sub

igold

 

[jtodd]

Try this... Again not the perfect answer...

Remember, test on backup copy of data.

Sub DelBlanks()

Dim wsT2 As Worksheet: Set wsT2 = Worksheets("Tracker2")
Dim wsT As Worksheet: Set wsT = Worksheets("Tracker")

wsT2.Range("d3:h431").Copy
wsT.Range("C3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
wsT.Range("C3:H431").SpecialCells(xlFormulas).Delete shift:=xlShiftUp
wsT.Range("C3:H431").SpecialCells(xlCellTypeBlanks).Delete
End Sub

igold

Sorry but I cannot get this to work ,
It copies and pastes data from Tracker 2 j3:n3 and all.
Changed the xlPasteAll to xlPasteValue and get a debug message??