CODEWITHSUNDEEP
vbaexcel
M.R.
M.R.

Reputation: 45

remove empty rows to the bottom

I'm trying to remove empty rows to the bottom. When I use the way below, I have a problem with the formulas in the table.

Make a new column to the right. Use the formula =IF(C2<>"",2,1) and fill down. Hide the column from prying eyes (just right-click on the grey column header at top to hide it) Sort by this column instead of C.

Thank you in advance!

Upvotes: 2

Views: 842

Answers (2)

user3598756
user3598756

Reputation: 29421

if column D is empty you could try this code:

Option Explicit

Sub main()
    With Range("C1", Cells(Rows.Count, 3).End(xlUp))
        .AutoFilter Field:=1, Criteria1:="<>"
        .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Copy
        .Offset(1, 1).PasteSpecial
        Application.CutCopyMode = False
        .Parent.AutoFilterMode = False
        .Value = .Offset(, 1).Value
        .Offset(, 1).ClearContents
    End With
End Sub

Upvotes: 0

KyloRen
KyloRen

Reputation: 2741

Try this,

It will delete and empty rows when there are empty cells in column c

Code 

Sub g()

 Dim r As Range
 Dim rng As Range

   For Each r In Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
      If r = "" Then
         If Not rng Is Nothing Then Set rng = Union(rng, r) Else Set rng = r
      End If
   Next r
    rng.EntireRow.Copy
    Range("A" & Cells(Rows.Count, 3).End(xlUp).Row + 1).PasteSpecial (xlPasteValues)
    rng.EntireRow.Delete


End Sub

Upvotes: 3

Related Questions