Deleting blanks with corresponding cells

[excelV]

I have a large amount data with dates in 2 columns (example below). I am trying to build a macro where it can detect the blank cells and then delete the blank with the corresponding date. I am able to delete the blanks but I can't get rid of the dates so my data will be skewed with missing data points.


activecell.offset(0,-2).columns("a:b").entirecolumn.select
selection.specialcells(xlcelltypeblanks).select
Selection.activecell.offset(0,-1).range("A1").select
selection.delete shift:= xlUp




10/7/2002 0.150943396
10/8/2002 0.124223602
10/10/2002
10/11/2002
10/15/2002 0.216216216
10/16/2002 0.050955414
10/17/2002 0.095238095

 

[jbeaucaire]

Can't you delete the whole row, wouldn't that be easier?

Range("B:B").SpecialCells(xlBlanks).EntireRow.Delete xlShiftUp

Notice there's no "selecting" or "activating" in this code, it's not necessary. You need to edit that stuff out of recorded code.

 

[excelV]

I wish I could delete the rows but I have multiple columns in one worksheet so I need a macro that can go through each set of columns and get rid of them. I thought "cutting" and "pasting" the bottom set of data above blank would be the easiest but I'm having trouble creating the macro.

Any recommendations would be appreciated!

 

[jbeaucaire]

Give this a try on a copy of your data:

Option Explicit

Sub Delete()
Dim DelRNG As Range
Dim LastCOL As Long
Dim LastROW As Long
Dim Col As Long
Dim Rw As Long

Application.ScreenUpdating = False
LastCOL = Cells(1, Columns.Count).End(xlToLeft).Column

For Col = 1 To LastCOL Step 2
    LastROW = Cells(Rows.Count, Col).End(xlUp).Row
    For Rw = 1 To LastROW
        If Cells(Rw, Col + 1) = "" Then
            If DelRNG Is Nothing Then
                Set DelRNG = Range(Cells(Rw, Col), Cells(Rw, Col + 1))
            Else
                Set DelRNG = Union(DelRNG, Range(Cells(Rw, Col), Cells(Rw, Col + 1)))
            End If
        End If
    Next Rw
    If Not DelRNG Is Nothing Then
        DelRNG.Delete xlShiftUp
        Set DelRNG = Nothing
    End If
Next Col

Application.ScreenUpdating = True
End Sub

 

[excelV]

That worked, thanks a lot!