excel - Finding the first row and then deleting the column if it matches the first row

Question

I’m trying to find the first row value of each column. Upon knowing the first value, I want to find whether the column contains the same value as the first row. If yes, I want to delete the entire column.

   lastR = ActiveSheet.Cells(Rows.Count,1).Row
LastC = ActiveSheet.Cells(1, Columns.Count).Column

For i to LastC
FirstRow=ActiveSheet.Range(Cells(4,1),Cells(4,LastC))
End i 

For x= LastC to 1 Step -1
If Worksheet.Function.CountIf(Columns(x), “FirstRow”)= LastR -1 Then 
Columns(x).EntireColumn.Delete 
End If

Next x

End Sub

I have an error 13 in the declaration of firstrow function. Why is it so?

question from:https://stackoverflow.com/questions/65878422/finding-the-first-row-and-then-deleting-the-column-if-it-matches-the-first-row

Answer 1

Here is a solution:

Sub Filter()
    Dim rRange As Range
    Dim rRangeColumn As Range
    Dim rRangeColumnData As Range   'Column without header (first cell)
    Dim rFirstCell As Range
    Dim rColumnData As Range   ' without header
    
    Set rRange = ActiveSheet.UsedRange
    For i = rRange.Columns.Count To 1 Step -1
        Set rRangeColumn = rRange.Columns(i)
        With rRangeColumn
            Set rRangeColumnData = Range(Cells(.Row + 1, i), Cells(.Rows.Count + rRange.Row - 1, i))
            Set rFirstCell = .Cells(1, 1)  ' header
            If Application.WorksheetFunction.CountIf(rRangeColumnData, rFirstCell) > 0 Then
                rRangeColumn.EntireColumn.Delete
            End If
        End With
    Next i
End Sub

By the way, your initial code contains an error (For...Next, and i is never used):

For i to LastC
FirstRow=ActiveSheet.Range(Cells(4,1),Cells(4,LastC))
Next i