simplify working code

[ndendrinos]

is there a way to simplify this code?
what it does is sorts within range pushing rows with empty values in A at the bottom and then clears them

Application.EnableEvents = False
[A17:F35].Select
With Selection

        .Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _
            Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom           
            
[A17:A35].Select
With Selection
.SpecialCells(xlCellTypeBlanks).EntireRow.ClearContents
End With
End With
    
    
Application.EnableEvents = True

 

[Trebor76]

Try this:

Range("A17:A35").Sort Key1:=Range("A17"), Order1:=xlAscending

Robert

 

[mirabeau]

Application.EnableEvents = False
With [A17:F35]
    .Sort .Cells(2, 1), 1
    .Resize(, 1).SpecialCells(4).EntireRow.ClearContents
End With
Application.EnableEvents = True

 

[ndendrinos]

thank you all.
Trebor76 for sorting
and mirabeau for sorting AND clearing

 

[Trebor76]

You're welcome. I didn't include code to clear because if the cells are actually null then there's no need to.

 

[ndendrinos]

thanks Trebor76
maybe I did not explained myself well (happens all the time)

the set up is like this:

......Qty....Shipped....B/O........Product....Cost.....Prod#


A17...2........ 1............1.........apple.......$1........ap1
A18......................................banana....$.95......bn6
A19...3.........2.............1........melons.....$1.75....mel1

after the code I need:

......Qty....Shipped....B/O........Product....Cost.....Prod#


A17...2........ 1............1.........apple.......$1........ap1
A19...3.........2.............1........melons.....$1.75....mel1

 

[mirabeau]

Nendrinos,

It was clear from your initial code that you wanted the entire row deleted when the cells in just Column A were empty, regardless of whether the other cells in the row were empty or not. No good assuming they're empty when you didn't specify it.

If you want the modification I suggested to be simplified further, swap around the two lines between the With... and End With. That's because there's fewer operations to be carried out if you delete first and then sort, rather than sorting first and then deleting. (May make a bit of difference with a large spreadsheet.)