Check Column For Data

[Jaye7]

Hi All,

I have the following script which deletes columns if the column count is empty.

Can someone please modify the script so that if the column has data but cell 1 of the column is empty then it will put the name "Location" in cell 1 of the column.

If the column count is 0 then it would still delete the column.

Sub New_Test()
Dim iCol As Integer
With ActiveSheet.UsedRange
For iCol = .Column + .Columns.Count - 1 To 1 Step -1
If IsEmpty(Cells(65536, iCol)) And IsEmpty(Cells(1, iCol)) Then
If Cells(65536, iCol).End(xlUp).Row = 1 Then Columns(iCol).Delete
End If
Next iCol
End With
End Sub

 

[Dryver14]

This works for me

Sub New_Test()
Dim iCol As Integer
With ActiveSheet.UsedRange
For iCol = .Column + .Columns.Count - 1 To 1 Step -1
If IsEmpty(Cells(65536, iCol)) And IsEmpty(Cells(1, iCol)) Then
If Cells(65536, iCol).End(xlUp).Row = 1 Then Columns(iCol).Delete
If Cells(1, iCol) = "" Then Cells(1, iCol) = "Location"
End If
Next iCol
End With
End Sub

 

[wigi]

My (different) approach which cuts down on loops and interaction with the sheet:

Sub New_Test()
    Dim rng As Range
    On Error Resume Next
    For Each rng In ActiveSheet.UsedRange.Rows(1).SpecialCells(xlCellTypeBlanks)
        rng.Value = IIf(Cells(Rows.Count, rng.Column).End(xlUp).Row = 1, True, "Location")
    Next
    ActiveSheet.UsedRange.Rows(1).SpecialCells(xlCellTypeConstants, xlLogical).EntireColumn.Delete
    On Error GoTo 0
End Sub

 

[Jaye7]

Thank you very much Dryver and Wigi, both are great, much appreciated.