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

 

[bobsan42]

...
For Each c In DataRange.Cells
    If Not IsNumeric(Left(c.Value, 1)) Then
        If DeleteRange Is Nothing Then
            Set DeleteRange = c
        Else
            Set DeleteRange = Union(DeleteRange, c)
        End If
    End If
Next c
...

 

[kithchain]

...
For Each c In DataRange.Cells
    If Not IsNumeric(Left(c.Value, 1)) Then
        If DeleteRange Is Nothing Then
            Set DeleteRange = c
        Else
            Set DeleteRange = Union(DeleteRange, c)
        End If
    End If
Next c
...

Hello, my apologies for late reply.

Your code works pretty well. Thank you so much

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Wondering how big your data set is likely to be? There could be faster ways if the data is large.
   
   
2. I notice that your code unhides the rows but it does that after the LR calculation is done. If rows at the end of the data are hidden, LR will not be the last row that contains data so the unhiding should be done before LR is calculated.
   
   
3. When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[kithchain]

Welcome to the MrExcel board!

1. Wondering how big your data set is likely to be? There could be faster ways if the data is large.
   
   
2. I notice that your code unhides the rows but it does that after the LR calculation is done. If rows at the end of the data are hidden, LR will not be the last row that contains data so the unhiding should be done before LR is calculated.
   
   
3. When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Hi Peter,

1. My data depends on how the "copy paste" will be from the PDF to excel. So basically my raw sheet would be like this when copy paste is done. See Reference 1 image.

2. I just only want cells that looks like from row 1 to 6 (from the Reference 1 image) to be visible for the entire sheet.

3. Noted on this. My apologies for my violations on the rules

 

[kvsrinivasamurthy]

Try this code

Sub DeleteRowsraw()
    Dim DataRange As Range
    Dim LR As Long
    Dim S As String
    Dim T As Long


    '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)
    DataRange.EntireRow.Hidden = False
    End With
  
    StartString = "number only"

For T = LR To 1 Step -1
k = Evaluate("Iserror(Find(Left(" & Range("F" & T).Address & ", 1),""0123456789""))")
If k = True Then S = S & "," & "F" & T
    If Len(S) > 250 Or (T = 1 And Len(S) > 0) Then
    Range(Mid(S, 2)).EntireRow.Delete: S = ""
    End If

Next T

End Sub

 

[Peter_SSs]

1. My data depends on how the "copy paste" will be from the PDF to excel. So basically my raw sheet would be like this when copy paste is done. See Reference 1 image.

So your data is likely to be less than 100 rows?

2. I just only want cells that looks like from row 1 to 6 (from the Reference 1 image) to be visible for the entire sheet.

Yet your original description seemed to be saying anything that starts with a number. The following cell from col F looks nothing like rows 1 to 6 but does start with a number. Should it be visible or not?

3. Noted on this. My apologies for my violations on the rules

You didn't violate any rules, it is just about making your post as easy as possible for people to help you.

 

[kithchain]

...
For Each c In DataRange.Cells
    If Not IsNumeric(Left(c.Value, 1)) Then
        If DeleteRange Is Nothing Then
            Set DeleteRange = c
        Else
            Set DeleteRange = Union(DeleteRange, c)
        End If
    End If
Next c
...

Hi Bobsan,

Just wandering if I can ask for your help again lol
I just updated my file and the code.

What happens now in the code is once I uploaded the file from PDF(see screenshot "Before") is I retain the rows from column A incase it is numeric all numeric, and would delete the row if the column B has dot "." in the cell (see highlighted yellow cells).

My 2 problems are:

1. Row is deleting as well incase column B has alphabet in the cell. (Which I also want to retain) (see highlighted green cells)
2. How can I fix the negative sign, it should be in the beginning of the numeric not in the end(see highlighted in red) because in the PDF the sign is in the end of the numeric

Before:

After:

Sub FixCol()
    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:B" & LR)
    End With
    StartString = 0
 
    DataRange.EntireRow.Hidden = False
 
    For Each c In DataRange.Cells
        If Not IsNumeric(Left(c.Value, 1)) Or Len(c.Value) <= 5 Or (Len(c.Value) > 5 And IsNumeric(c.Value) And c.Column = 1 And InStr(c.Offset(0, 1).Value, ".") > 0) 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
    
 
End Sub

 

[Alex Blakenburg]

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

 

[Alex Blakenburg]

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