vba delete Row based on

[panyagak]

hi everyone.

I need to clean up my long data in column A.

The word "DIM" STARTS the next set of data downwards AND BEFORE IT is a Number.
eg.
DIM
2236.2
DIM
Vlogger
DIM. etc etc

so I need to delete Rows just before DIM in instances where IT IS NOT A VALUE. Eg above, Delete Vlogger Row since its not a number.

the column A is long down..

Thanks

 

[nemmi69]

Dim Wb As Workbook
Dim WsIn As Worksheet
Dim LastRow As Long
Dim Aloop As Long
Dim DIMrow As Boolean

Sub DeleteNonNum()
    Set Wb = ThisWorkbook
    Set WsIn = Wb.Worksheets("Sheet1")
    LastRow = WsIn.Cells(WsIn.Rows.Count, "A").End(xlUp).Row
    DIMrow = False
 
    For Aloop = LastRow To 1 Step -1
       If DIMrow = True Then
           If Not IsNumeric(WsIn.Cells(Aloop, 1).Value) Then
               WsIn.Rows(Aloop).Delete Shift:=xlUp
           End If
           DIMrow = False
       Else
           If WsIn.Cells(Aloop, 1) = "DIM" Then
               DIMrow = True
           End If
       End If
    Next Aloop
 
End Sub

 

[panyagak]

View attachment 103499
View attachment 103500

Dim Wb As Workbook
Dim WsIn As Worksheet
Dim LastRow As Long
Dim Aloop As Long
Dim DIMrow As Boolean

Sub DeleteNonNum()
    Set Wb = ThisWorkbook
    Set WsIn = Wb.Worksheets("Sheet1")
    LastRow = WsIn.Cells(WsIn.Rows.Count, "A").End(xlUp).Row
    DIMrow = False
 
    For Aloop = LastRow To 1 Step -1
       If DIMrow = True Then
           If Not IsNumeric(WsIn.Cells(Aloop, 1).Value) Then
               WsIn.Rows(Aloop).Delete Shift:=xlUp
           End If
           DIMrow = False
       Else
           If WsIn.Cells(Aloop, 1) = "DIM" Then
               DIMrow = True
           End If
       End If
    Next Aloop
 
End Sub

nemmi69​

thanks a lot...it worked

 

[nemmi69]

Glad to help