Macro that removes blank rows?

[taylorsm]

I have a log that is A:N and varies on how many rows. I have a macro (below) that combines my several logs into one mother log. Problem is, if one of my reps skips a line then this macro will not grab the stuff below that. Is there a way to remove those accidental skips, or would it be better for my macro to be altered to accommodate for those? Even though it is just a blank sheet with a few headers, I have attached what we use.

Sub DCMRCombine()
Dim J As Long, k As Long, r As Range
J = Worksheets.Count
With Worksheets("DCMR")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
r.EntireRow.Delete
End With

For k = 1 To J
If Worksheets(k).Name = "DCMR" Then GoTo errorhandler
With Worksheets(k)
If .Range("A2") = "" Then GoTo errorhandler
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
r.EntireRow.Copy
Worksheets("DCMR").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
errorhandler:
Next k
' Center Macro
'

'
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

[CK76]

Below portion of your code after "With Worksheets(k)" line, is what stops range at next empty row.

Set r = Range(.Range("A2"), .Range("A2").End(xlDown))

Change to...

Set r = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Rows)

[taylorsm]

CK76

When I do this, I get a 1004 error. Application defined or object defined error

Sub DCMRCombine()
Dim J As Long, k As Long, r As Range
J = Worksheets.Count
With Worksheets("DCMR")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
r.EntireRow.Delete
End With

For k = 1 To J
If Worksheets(k).Name = "DCMR" Then GoTo errorhandler
With Worksheets(k)
If .Range("A2") = "" Then GoTo errorhandler
Set r = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Rows)
r.EntireRow.Copy
Worksheets("DCMR").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
errorhandler:
Next k
' Center Macro
'

'
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

[alansidman]

Run this macro to delete any blank rows

Sub delBlank()
Dim i As Long, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If IsNull(Range("A" & i)) Then Range("A" & i).EntireRow.Delete
Next i
End Sub

[taylorsm]

alansidman

This didn't seem to work. I filled in about 30 rows with data, then selected about 5 random rows and delete the data out. Ran this macro and nothing happened.

[davesexcel]

Instead of xldown, count the rows.

Sub Button2_Click()
    Dim r As Range, sh As Worksheet, LstRw As Long
    Set sh = Sheets("Sheet1")
    With sh
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set r = .Range("A2:A" & LstRw)
    End With
    r.Select
End Sub

[taylorsm]

davesexcel

Similar thing as Alan's. I filled in about 30 rows with data, then selected about 5 random rows and delete the data out. Ran this macro and nothing happened.

this was on a new blank workbook.

[CK76]

I had no issue running the code. At any rate I'd suggest going with how davesexcel showed you and defining last row in separate line.

Sub DCMRCombine()
Dim J As Long, k As Long, r As Range
J = Worksheets.Count
With Worksheets("DCMR")
lRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set r = .Range("A2:A" & lRow)
r.EntireRow.Delete
End With

For k = 1 To J
If Worksheets(k).Name = "DCMR" Then GoTo errorhandler
With Worksheets(k)
If .Range("A2") = "" Then GoTo errorhandler
lRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set r = .Range("A2:A" & lRow)
r.EntireRow.Copy
Worksheets("DCMR").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
errorhandler:
Next k
' Center Macro
'

'
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
End Sub

[taylorsm]

But I can't define the last row, the last row may be row 75 on one sheet, or row 50 on another sheet.

[davesexcel]

Not sure what you mean, the code counts the rows. It finds the very last filled row in column A

[bakerman2]

How about this ?

Sub DCMRCombine()
    Dim k As Long, ws As Worksheet, lRow As Long
    Set ws = Sheets("DCMR")
    With ws
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lRow <> 1 then .Rows("2:" & lRow).Clear
    End With

    For k = 1 To Sheets.Count
        If Sheets(k).Name = "DCMR" Then GoTo errorhandler
        With Sheets(k)
            If .Range("A2") = "" Then GoTo errorhandler
            lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Range("A2:N" & lRow).Copy ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
        End With
errorhandler:
    Next k
' Center Macro
'

'
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
End Sub

The reason why is quite simple.
At the start of your code you clear the contents of sheet DCMR. If you then do a Rows.Count without specifying on which sheet and DCMR is the active one when running the code you always get 1 for your LastRow count.
Therefor nothing happens on your other sheets.

[taylorsm]

Thanks bakerman, that works well, only issue is that it also copies the blank row over. So then when I run a pivot table off the data, the table wants to stop at the blank row.

[bakerman2]

Alan posted code in Post#3 to delete blank rows. Use that after the copying.