VBA code to delete copied blank cells

[KevinMMO]

Hi, I'm doing a macro that copies a specific range and then pastes it on another sheet to make a database. This range contains blank cells and also another ones that have a formula that if a cell is blank, then the value of the cell's formula is blank. If(B7="","",.....).

the range that I copy is from C7:E57. all this cells have a formula like that above. They all depend on the "B" column's value, but the person doesn't always fill the 50 rows. Here's the code:

Public Sub copiar()   'macro terminado para copiar datos e insertarlos en base de datos


'Declare Variables
Dim wsOrigin As Worksheet
Dim wsDataBase As Worksheet
Set wsOrigin = ThisWorkbook.Sheets("Recoleccion")
Set wsDataBase = ThisWorkbook.Sheets("Basedatos")

Application.ScreenUpdating = False    'para que código sea más rápido

'Copy/Special Paste para datos

Dim COPYME As Range

Dim contador As Long




contador = wsDataBase.Range("A" & wsDataBase.Rows.Count).End(xlUp).Row

Set COPYME = wsOrigin.Range("C7:E57")   'Rango que se va a copiar

COPYME.Copy

wsDataBase.Range("A" & contador + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False




Application.ScreenUpdating = True

End Sub

Is there a way to delete the blank cells copied to the database sheet, so when the macro runs again it doesn't have all that blank spaces? Without doing it manually.

If this isn't explained very clearly, I could try being more specific if asked. Thanks.

 

[Osvaldo Palmeiro]

Hi. Add the red parts and give it a try.


Dim wsOrigin As Worksheet, k As Long



wsDataBase.Range("A" & contador + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

With wsDataBase
For k = .Cells(Rows.Count, 1).End(xlUp).Row To contador + 1 Step -1
If .Cells(k, 1) = "" Then .Rows(k).Delete
Next k
End With

Application.ScreenUpdating = True

 

[KevinMMO]

It works, Thanks!

Hi. Add the red parts and give it a try.


Dim wsOrigin As Worksheet, k As Long



wsDataBase.Range("A" & contador + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

With wsDataBase
For k = .Cells(Rows.Count, 1).End(xlUp).Row To contador + 1 Step -1
If .Cells(k, 1) = "" Then .Rows(k).Delete
Next k
End With

Application.ScreenUpdating = True