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

user_14989578 0
Viewed 30

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?

vba excel
1 Answers

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
user_132217860
Related