[SOLVED] Remove columns if the number of rows is less than a certain number using vba Excel

Remove columns if the number of rows is less than a certain number using vba Excel

I have an Excel file with a lot of columns. I want to create a loop (B1:LastCol) using vba that goes through the amount of filled columns, and that deletes the columns that contain less than a certain numbers of rows (for example, columns with less than 50 rows will be removed as they are incomplete measurements). I am not sure how I can handle this. I included some corrupt code, to give a better idea of what I want.

Sub test()
 Dim LastColNr As Long
 Dim LastCol As String
 LastColNr = Graphs.Cells(1, Columns.Count).End(xlToLeft).Column
 LastCol = GetColumnLetter(LastColNr)
 
 MsgBox "Column Count: " & LastColNr  & LastCol
 
 Dim cel As Range
 Dim rng As Range
 
 For Each rng In Range("B1: LastCol").Columns
     If rng("B1", rng("B1").End(xlDown)).Rows.Count > 50
       Column.Delete
     End If
 Next rng
End Sub

Solution

Here is how I would do it assuming that there is data in Column A.

Sub Test()
    Application.ScreenUpdating = False
    Dim Target As Range
    Set Target = Graphs.UsedRange.Offset(, 1).EntireColumn
    
    Dim c As Long
    For c = Target.Columns.Count To 1 Step -1
        If WorksheetFunction.CountA(Target.Columns(c)) < 50 Then Target.Columns(c).Delete Shift:=xlToLeft
    Next
    
End Sub