VBA deleterows

[kithchain]

Hi, I just wanted to ask how can I retain the rows if column F or StartString(Font in red) starts with number. Below is my code.

Sub DeleteRowsraw()
    Dim c As Range, DeleteRange As Range, DataRange As Range
    Dim LR As Long
    Dim i As Long
    Dim StartString As String
  


    'change sheet name as required
    With Worksheets("Sheet1")
        'find last row in range
        LR = .Cells(.Rows.Count, "F").End(xlUp).Row
      
        'range you are searching
        Set DataRange = .Range("F1:F" & LR)
      
    End With
  
    StartString = "number only"


    DataRange.EntireRow.Hidden = False


    For Each c In DataRange.Cells
        If Left(c.Value, 1) <> StartString Then
            If DeleteRange Is Nothing Then
                Set DeleteRange = c
            Else
                Set DeleteRange = Union(DeleteRange, c)
            End If
        End If
    Next c
    'delete all matched rows in one go
    If Not DeleteRange Is Nothing Then DeleteRange.EntireRow.Delete
  
  
  
End Sub

 

[kithchain]

Can you rely on every detail line in column A being a 10 digit number ? Also what are the numbers surrounded in "*" ?

Hi Alex,

I believe yes, based on history of the transactions it always a 10 digit number, my concern is just incase not 10 digit it wont catch the row.

The numbers surrounded by "*" are just a subtotal/total for the table in the PDF. But I dont really need it. That is why my "if" function is to find dot "." in the column B and delete the entire row. But not sure why the row is deleted as well incase column B are alphanumeric lol. Thank you in advanced.

 

[kithchain]

Hi Alex,

I tried the code. It worked pretty well but it retains the row you are asking with "*". Just need to delete that as well.

If you can then you can give this a try:

Sub FixCol_mod()
    Dim c As Range, DeleteRange As Range, DataRange As Range
    Dim LR As Long
    Dim i As Long
    Dim StartString As String
 
    ' Change sheet name as required
    With Worksheets("Sheet1")
        ' Find last row in range
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' Range you are searching
        Set DataRange = .Range("A2:F" & LR)
    End With
    StartString = 0
 
    DataRange.EntireRow.Hidden = False
 
    For Each c In DataRange.Columns(1).Cells
        If Not IsNumeric(c.Value) Or Len(Trim(c.Value)) <> 10 Then
            If DeleteRange Is Nothing Then
                Set DeleteRange = c.EntireRow
            Else
                Set DeleteRange = Union(DeleteRange, c.EntireRow)
            End If
        End If
    Next c
 
    ' Delete all matched rows in one go
    If Not DeleteRange Is Nothing Then DeleteRange.Delete
   
    For i = 4 To 6
        DataRange.Columns(i).TextToColumns Destination:=DataRange.Columns(i), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
    Next i
   
End Sub

 

[Alex Blakenburg]

If the use of "*" is reliable you can change the if statment to this:

        If Not IsNumeric(c.Value) Or Len(Trim(c.Value)) <> 10 Or InStr(1, c.Offset(, 3), "*") <> 0 Then

 

[kithchain]

If the use of "*" is reliable you can change the if statment to this:

        If Not IsNumeric(c.Value) Or Len(Trim(c.Value)) <> 10 Or InStr(1, c.Offset(, 3), "*") <> 0 Then

Hi, Alex!

Your code works perfectly as I wanted! Appreciate your time. Thank you!

 

[Alex Blakenburg]

It was a team effort . Glad we could help.

 

[smozgur]

Hi, Alex!

Your code works perfectly as I wanted! Appreciate your time. Thank you!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.