CODEWITHSUNDEEP
vbaexcel
ProfessorMole
ProfessorMole

Reputation: 3

Adapting a cell deleting macro to cover numerous columns

I have a workbook right now that does the following processes:

  1. After data is entered in [Sheet 1], macro creates a record of it in [Sheet 2] at the bottom of the dataset.
  2. Based on numerous columns of formulas in [Sheet 2], a new dataset is taken from [Sheet 2] and copied as values into [Sheet 3].

Up to this point, everything is running correctly.

In [Sheet 3], each column is going to be individually treated for drop down purposes. Once the data gets to [Sheet 3], each of these columns could have several blanks that are copied over from [Sheet 2] that cause it to sort with all of those blanks ahead of the actual data making the drop down ugly.

I have created a macro to remove those blanks, but it's only doing it for the first column:

Sub Delete_Cell()
Dim lrow As Long
For lrow = Cells(Cells.Rows.Count, "A").End(xlUp).row To 2 Step -1
If Cells(lrow, "A") = "" Then Cells(lrow, "A").delete
Next lrow
End Sub

I need an adaptation to this macro that will tell it to do this for columns A through J.

Any help is appreciated.

Thanks

Upvotes: 0

Views: 34

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub Delete_Cell()
    Dim lrow As Long, lcol As Long
    For lcol = 1 To 10
        For lrow = Cells(Rows.Count, lcol).End(xlUp).Row To 2 Step -1
            If Cells(lrow, lcol) = "" Then
                Cells(lrow, lcol).Delete
            End If
        Next lrow
    Next lcol
End Sub

Upvotes: 1

Related Questions