VBA loop not working to delete blank and Bad Character Rows

Question

Hello so I am trying to help the workflow at my job. We have to import two files not .txt that come from two different databases. I created an excel macro that imports the two files with a refresh using get external function option. I would delimit them but that's later. Next I would like to go through and delete the cells that have blanks and bad characters in Column A Starting from row A2. I'm really new and haven't had to script in a longtime due to my job. This is what I have and have trying to tweak. Now the loop deletes almost everything!! Please help. Yes I have looked on all of the forums for help and nothing has worked.

Sub DeleteBadRows()
    Dim i As Variant
    Dim RowNbr As Long
    Dim ColNbr As Long
    Dim BadChr() As Variant
    Dim LR As Long

    BadChr = Array("=", "*", ",FEE", "DATE 12/13", ",(", "SMSLIST O", "REQUEST T", "WHERE", "SVC")  'include any characters to trigger deletion of row
    LR = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    For RowNbr = LR To 1 Step -1
        For ColNbr = 1 To Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Column
            For i = LBound(BadChr) To UBound(BadChr)
                If InStr(Cells(RowNbr, ColNbr), BadChr(i)) Then
                Cells(i).EntireRow.Delete

                    Exit For
                End If
            Next i
        Next ColNbr
    Next RowNbr

Patrick Honorez · Accepted Answer · 2017-12-22 14:43:52Z

1

What about Rows(LR).Delete instead of Cells(i).EntireRow.Delete ?

answered Dec 22, 2017 at 14:43

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vityata Over a year ago

Yup, this is the part of it exactly. I saw the first error, did not see the second :)

Vityata · Accepted Answer · 2017-12-22 14:50:47Z

0

You are actually deleting the position from the Array, not the row. Thus, you are making two errors.

So try with:

Cells(RowNbr, 1).EntireRow.Delete

Instead of :

Cells(i).EntireRow.Delete

There is a small "tricky trick" in VBA.

When you give 2 parameters to the Cells() then the first is a row and the second is a column. However, if you only give one parameter, then it is a column.

Thus Cells(5) is E1 and not A5 as you are probably expecting. Thus it deletes the first cell from your code almost always.

Furthermore, the way you are looking for the last column is recalculated every time and it can be dangerous. Try to use a variable there:

For ColNbr = 1 To Cells.Find("*", so:=xlByRows, searchdirection:=xlPrevious).Column

edited Dec 22, 2017 at 14:50

answered Dec 22, 2017 at 13:43

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

12 Comments

SJR Over a year ago

To confuse further, Range("A1:A5").Cells(5) is A5.

Kera Over a year ago

I tried that and it's still deleting the rows other than the ones defined. It worked before.Could it be that the Array is too large? The data maybe?

Vityata Over a year ago

@Kera - I suppose that Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Column is supposed to give you the right end column. Give it a try, hardcode it with 100 and let the VBA code run again.

Kera Over a year ago

Now I'm getting a Type mismatch at this line of the code: If InStr(Cells(RowNbr, ColNbr), BadChr(i)) Then

Vityata Over a year ago

@Kera - what are the values of RowNbr and ColNbr at the specific moment, on which you are getting the type missmatch

|

QHarr · Accepted Answer · 2017-12-22 15:17:57Z

0

So i have put it like this and removed from the array "*" as this meant all rows were being deleted

Option Explicit

Public Sub DeleteBadRows()

    Dim RowNbR As Long
    Dim BadChr() As Variant
    Dim LR As Long
    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim lastCol As Long

    Set wb = ThisWorkbook
    Set wsSource = wb.Worksheets("Sheet1")

    Dim rngDelete As Range
    BadChr = Array("=", ",FEE", "DATE 12/13", ",(", "SMSLIST O", "REQUEST T", "WHERE", "SVC") 'include any characters to trigger deletion of row

    LR = wsSource.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastCol = wsSource.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column

    For RowNbR = 1 To LR

        If WasFound(wsSource, BadChr, RowNbR) Then

            If Not rngDelete Is Nothing Then

                Set rngDelete = Union(rngDelete, wsSource.Cells.Rows(RowNbR).EntireRow)

            Else
                Set rngDelete = wsSource.Cells.Rows(RowNbR).EntireRow

            End If
        End If


    Next RowNbR

    If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
End Sub


Private Function WasFound(ByRef wsSource As Worksheet, ByVal BadChr As Variant, ByVal RowNbR As Long) As Boolean
 Dim i As Long

 WasFound = False

 For i = LBound(BadChr) To UBound(BadChr)

          Dim found As Long

            On Error Resume Next
            found = wsSource.Cells(RowNbR, 1).EntireRow.Find(What:=BadChr(i), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
            On Error GoTo 0

            If found > 0 Then

                 WasFound = True

                Exit Function
            End If

       Next i

End Function

edited Dec 22, 2017 at 15:17

answered Dec 22, 2017 at 14:47

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

2 Comments

Kera Over a year ago

I tried this code and it seems to work but I think the megbox has to go, lol. This file is big and I'm up to number 180

Kera Over a year ago

I took the message box out and it deleted everything except one line.

Collectives™ on Stack Overflow

VBA loop not working to delete blank and Bad Character Rows

3 Answers 3

1 Comment

12 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related