[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Delete blank rows sub

Hi,

I have attached a ss where i want a piece of code that can count until the last row using ColA and then delete any blank rows (there is formula in the blank rows)

See attached

Thanks
Seamus
test1.xls
0
Seamus2626
Asked:
Seamus2626
  • 2
1 Solution
 
Rory ArchibaldCommented:
Something like:
Sub TidyUp()
   With Sheets("Sheet1")
    .Range("A1").CurrentRegion.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
      .Range(.Cells(.Rows.Count, "A").End(xlUp).Offset(1), .Cells.SpecialCells(xlCellTypeLastCell)).Clear
   End With
End Sub

Open in new window

0
 
Seamus2626Author Commented:
Magic

Thanks Rory

Seamus
0
 
Russell LucasIT Infrastructure Project ManagerCommented:
Just as an alternative solution:-


Sub Remove_Blanks()
Dim i As Integer
i = 2
While ExitCondition = False
    If Sheet1.Range("I" & i).Formula = "" Then ExitCondition = True
    If Sheet1.Range("A" & i).Value = "" Then
        Sheet1.Rows(i).Delete
    Else
        i = i + 1
    End If
Wend
End Sub

Open in new window

0
 
Seamus2626Author Commented:
Thanks Funinig_Stroll, appreciate the additional info

Cheers,
Seamus
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now