How to find empty rows between two rows and remove those rows and leave just one empty row?

[ironny90]

I know this is a simple question, but how to find the empty rows in between and remove all but one? I used a very redundant method - find the cells that are above and below the empty rows, offset 1 or 2, select the rows and delete.

Is there a way to find the first/last cell with content? I think I saw it somewhere, just not sure where Thank you!!!

 

[Joe4]

I think it would be most beneficial to us if you show us a small sample of what your data looks like to start, and your expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[BSALV]

add an empty column at the RHS with a formula, autofilter that column and delete the visible rows

Sub One_Empty_Row()
     With ActiveSheet
          Set c = .UsedRange
          With c.Resize(, 1).Offset(, c.Columns.Count)
               .Offset(1).Resize(.Rows.Count - 1).FormulaR1C1 = "=IF(COUNTA(RC1:RC[-1])=0,sum(R[-1]C,1),0)"
               .Cells(1, 1).Value = "header"
               .AutoFilter 1, ">1"
               .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
               .AutoFilter
               .Resize(.Rows.Count + 1).ClearContents
          End With
     End With
End Sub

 

[ironny90]

add an empty column at the RHS with a formula, autofilter that column and delete the visible rows

Sub One_Empty_Row()
     With ActiveSheet
          Set c = .UsedRange
          With c.Resize(, 1).Offset(, c.Columns.Count)
               .Offset(1).Resize(.Rows.Count - 1).FormulaR1C1 = "=IF(COUNTA(RC1:RC[-1])=0,sum(R[-1]C,1),0)"
               .Cells(1, 1).Value = "header"
               .AutoFilter 1, ">1"
               .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
               .AutoFilter
               .Resize(.Rows.Count + 1).ClearContents
          End With
     End With
End Sub

Thank you! this is a good way! nice code.