Vba to delete blank rows only

[decadence]

Hi, I need a macro that can delete all empty rows in a worksheet, but not the empty cells that are in a row with data. I have tried but the closest I have got is the rows being deleted 1 at a time from bottom up on a loop and my pc froze. Can someone help me please.

 

[Peter_SSs]

Really?

Yes, what I was mainly trying to establish was whether you were selecting the final data manually, or perhaps doing it via other code.

You said earlier that my code "kind of works". That's a bit vague so, do you agree that my code removes all the blank rows?

- If it doesn't, could you explain that more and/or give a small sample data where it fails?

- If it does, then it seems to me you have a solid block of data, which you are manually selecting. If you only select to the end of the solid block of data, I don't see how extra (blank) data can turn up when you paste into Notepad, I certainly haven't been able to replicate that. In fact, even if I select my data AND blank columns to the right AND blank rows below, when I paste into Notepad those extra blank column & rows do not appear. Pressing Ctrl+End in Notepad takes my cursor to the beginning of the line immediately below the last line of data. For me, that is always the case when pasting into Notepad from Excel.

I'm not sure what I'll be able to do with your answer, but when you say there are "spaces at the end of my data in Notepad" can you elaborate on that?
- Are there blank rows? How many? Does it relate to how many empty rows were deleted?
- Are there blank spaces after each item of data?
- Something else?

 

[decadence]

Hi peter, I have 674 rows and 5 columns of data in this instance. after using the remove duplicate macro previously posted, there will be 169 rows. so if I copy over the data in notepad there will be 674 rows worth of data including the blank rows from the duplicate rows that were removed, hence why I have spaces at the end of the data in notepad.
So I then use a macro to delete blank rows up to the last cell with data in to get rid of the excess blank rows, but the macro you posted doesn't do that for this, but does work generally with a random data test hence the "Kind of works".

So when I Shift End my data it still goes to row 674 as if it still believes something is still in the cells, Also I don't have any spaces at the end of each Item of data

 

[JackDanIce]

I wonder if the issue is with what Excel recognises as the last used cell on the worksheet and should reset this, post removal of duplications.

Try:

Sub RemoveDuplicateRows()

    Dim x   As Long
    Dim LR  As Long
    Dim dic As Object
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    Application.ScreenUpdating = False
        
    LR = Range(rows.count, 1).End(xlUp).row
    For x = 1 To LR
        If dic.exists(Cells(x, 1).value) Then
            Cells(x, 1).ClearContents
        Else
            dic(Cells(x, 1).value) = 1
        End If
    Next x
    
    Cells(1, 1).Resize(LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    'Reset last used cell of worksheet
      ActiveSheet.UsedRange
    ThisWorkbook.Save
 
    Application.ScreenUpdating = True
    
    Set dic = Nothing
    
End Sub

 

[decadence]

Hi Jack, this macro doesn't work. I get this error -
Run-time error '1004':
Method 'Range' of object'_Global' failed

 

[JackDanIce]

Try:

Sub RemoveDuplicateRows()

    Dim x   As Long
    Dim LR  As Long
    Dim dic As Object
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    Application.ScreenUpdating = False
        
    LR = Cells(rows.count, 1).End(xlUp).row
    For x = 1 To LR
        If dic.exists(Cells(x, 1).value) Then
            Cells(x, 1).ClearContents
        Else
            dic(Cells(x, 1).value) = 1
        End If
    Next x
    
    Cells(1, 1).Resize(LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    'Reset last used cell of worksheet
      ActiveSheet.UsedRange
    ThisWorkbook.Save
 
    Application.ScreenUpdating = True
    
    Set dic = Nothing
    
End Sub

 

[Peter_SSs]

So when I Shift End my data it still goes to row 674 as if it still believes something is still in the cells, Also I don't have any spaces at the end of each Item of data

I presume that you meant Ctrl-End rather than Shift-End. In any case, when you said you said "highlight the data on my sheet" you were actually highlighting all your data and more.

Try adding this line which should reset the last cell to the correct row.

End With
  ActiveWorkbook.Save
  Application.ScreenUpdating = True
End Sub

]