Delete rows with cells that have same value

[NMGMX]

I have a sheet with rows that extend to about Column AT. In some of the rows, all the cells are populated with "--". I want to delete these rows. Other rows have "--"s but not in every cell. I tried to remove all the "--"s then running this code:
For t = 2 To Workbooks(WorkbookNm).Sheets("--").UsedRange.Rows.Count j = CStr(t)
If Application.CountA(Workbooks(WorkbookNm).Sheets("--").Range("A" & j).EntireRow) = 0 Then
Rows(t & ":" & Workbooks(WorkbookNm).Sheets("--").UsedRange.Rows.Count).Delete
GoTo Step15__2:
End If
It worked, but I had trouble re-adding the "--"s. So I'm wondering if there is a simple way to modify the above code, so that it looks for and deletes rows with only "--" and possibly nothing in the cells (e.g. Column AU and on)?

 

[NeonRedSharpie]

[COLOR=#333333]For t = 2 To Workbooks(WorkbookNm).Sheets("--").UsedRange.Rows.Count
j = CStr(t)[/COLOR]
[COLOR=#333333]If Application.CountA(Workbooks(WorkbookNm).Sheets("--").Range("A" & j).EntireRow) = 0 Then[/COLOR]
[COLOR=#333333]     Rows(t & ":" & Workbooks(WorkbookNm).Sheets("--").UsedRange.Rows.Count).Delete[/COLOR]
[COLOR=#333333]GoTo Step15__2:[/COLOR]
[COLOR=#333333]End If[/COLOR]

I think this is the formatting for your code. I have no idea what the code is trying to do. Also, why are you re-adding the "--"? So basically, if every cell in the row is a "--", you want to delete it. Use a for loop like:

Sub removeDashes()

    Dim dashes As String
    Dim rowMax As Integer
    Dim counter As Integer
    
    rowMax = ActiveSheet.UsedRange.Rows.Count
    
    dashes = "--"
    For y = 2 To rowMax
        For x = 1 To 47
            If x = 1 Then
                counter = 0
            ElseIf x = 47 And counter = 1 Then
                Rows(y & ":" & y).Delete
            End If
        
            If Cells(y, x) <> dashes Then
                counter = 1
            End If
        Next
    Next
    


End Sub

 

[CodeNinja]

NMGMX,
When I have to do these kinds of deletions, I kinda cheat. I found my code ran a lot faster if I filtered to the stuff I wanted to keep, copied the data and pasted it into a new sheet. If you want, you can always delete the data out of your original sheet and paste it back in from the new sheet.

That would look something like this... Modify code to suit your needs:

dim ws as worksheet
dim lastRow as integer
dim i as integer

set ws = workbooks(WorkbookNM).sheets("--")
lastrow = ws.usedrange.rows.count

ws.rows("1:1").autofilter
for i = 1 to 10 'loop through columns A - J
    ws.range("A1:J" & lastrow).autofilter Field:=i, Criteria1:="<>--", Operator:=xlOr
next i
ws.usedrange.copy
workbooks(WorkbookNM).sheets.add after:=workbooks(workbookNM).sheets(workbooks(workbookNM).sheets.count)
workbooks(workbookNM).sheets(workbooks(workbookNM).sheets.count).activate
activesheet.paste

 

[NMGMX]

Thank you for the replies. What I was doing in the code was this: I had removed all "--"'s and replaced them with "" (blank). This code then searched for blank rows and deleted them (because the "--" rows are now blank). Unfortunately I was having trouble re-adding the "--"'s in other rows that were supposed to be there. This has been fixed now but if you can give me a faster code then I'd be grateful.
In short:
Data:

1. I replaced all "--" w/ "" (Not shown in code)
2. I delete the now-blank rows (3 in this example, The code)
3. I re-add the other "--"s that weren't supposed to be deleted (E.g. non all-"--" rows (the top 2), Not show in code)
If you can give me a faster code which does this:
1. Delete the only all-"--" rows.
That would be awesome.

 

[NMGMX]

Just realized my code actually didn't work.

 

[NMGMX]

I still need help.

 

[NeonRedSharpie]

Sub removeDashes()

    Dim dashes As String
    Dim rowMax As Integer
    Dim counter As Integer
    
    rowMax = ActiveSheet.UsedRange.Rows.Count
    
    dashes = "--"
    For y = 2 To rowMax
        For x = 1 To 47
            If x = 1 Then
                counter = 0
            ElseIf x = 47 And counter = 0 Then
                Rows(y & ":" & y).Delete
            End If
        
            If Cells(y, x) <> dashes Then
                counter = 1
            End If
        Next
    Next
    


End Sub

This code will do that, assuming the blanks are from column 1 to column 47. 47 being AU. If the cells are only filled from B to AP, change the x loop to "2 to 42"

Though for some reason it needs to be run multiple times to actually remove them all...not sure why though.

 

[MrKowz]

Try this.

Public Sub ClearRows()
Dim i       As Long, _
    j       As Long
    
Dim LR      As Long, _
    LC      As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Application.CountIf(Range("A" & i & ":AT" & i), "--") = Range("AT" & i).Column Then
        Rows(i).Delete shift:=xlUp
    End If
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[MrKowz]

Here's something a bit more modular. Searches for the last column in each row and if the entire row is populated with "--", then it deletes that row.

Public Sub ClearRows()
Dim i       As Long, _
    j       As Long
    
Dim LR      As Long, _
    LC      As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    LC = Cells(i, Columns.Count).End(xlToLeft).Column
    If Application.CountIf(Range("A" & i & ":AT" & i), "--") = LC Then
        Rows(i).Delete shift:=xlUp
    End If
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[NMGMX]

Hey thanks for replying and sorry I didn't get back for so long. Anyway, I did find a way to do what I had originally wanted to do.
1. I replaced all "--" w/ "" (Not shown in code)
2. I delete the now-blank rows (The code)
3. I re-add the other "--"s that weren't supposed to be deleted (E.g. non all-"--" rows, Not show in code)
I just had to select the range that I wanted to perform the replacement function on.