Delete column if row is blank

[rozak]

Leave rows 1-4 untouched for the header. Then I want to use row 5 to mark the columns to keep i.e. if row 5 is empty, delete that column from row 5 down. I'm getting an "Expected...Array" message for some reason. Thanks in advance.

Sub DeleteBlankColumns()        'delete columns if row 5 is blank
 
  Dim myLastCol As Long         '
  Dim myLastRow As Long
  Dim myCol As Long
 
  myLastCol = Cells(5, Columns.Count).End(xlToLeft).Column
 
  For myCol = myLastCol To 1 Step -1

      myLastRow = Cells(Rows.Count, myCol).End(xlUp).Row
      If myLastRow(Cells(5, myLastRow).Value, "") Then
      Columns(myLastRow).Delete Shift:=xlToLeft
      End If
      
  Next myCol
 
End Sub

 

[Fluff]

Are you trying to delete the entire column (including the header, or just clear the cells below the header?

 

[rozak]

Are you trying to delete the entire column (including the header, or just clear the cells below the header?

I want to leave the header in rows 1 to 4 as is, then clear the cells below. Thanks

 

[footoo]

Sub DeleteBlankColumns()
Dim a%
With Rows(5).SpecialCells(xlCellTypeBlanks)
    For a = .Areas.Count To 1 Step -1
        Intersect(ActiveSheet.UsedRange, .Areas(a).Resize(Rows.Count - 4)).Delete
    Next
End With
End Sub

 

[Fluff]

Ok, how about

Sub rozak()
   Intersect(Rows("5:1048576"), Rows(5).SpecialCells(xlBlanks).EntireColumn).Value = ""
End Sub

 

[rozak]

Fluff you gave me exactly what I asked for (sorry, I wasn't clear). Footoo read my mind and gave me what I wanted. Thank you both. Much appreciated.

 

[Fluff]

Glad it's sorted & thanks for the feedback.