How to stop blank rows posting at top of custom sort macro

[DaveLansley]

Hi

When running the below code, I want to sort first by column B and then by column A, with results appearing at the top of the worksheet (underneath the header). Instead, there are lots of blank rows before the results actually appear. I know very little about VBA and was hoping someone could make the blank rows disappear!

Sub sort()
'Descending sort on A:H using columns B and A, all sheets in workbook
Dim WS As Worksheet
For Each WS In Worksheets
WS.Columns("A:H").Sort Key1:=WS.Columns("B"), Order1:=xlAscending, _
Key2:=WS.Columns(“A”), Order2:=xlAscending, Header:=x1No
Next WS
End Sub

 

[oldbrewer]

Sub sort()
'Descending sort on A:H using columns B and A, all sheets in workbook
Dim WS As Worksheet
For Each WS In Worksheets
WS.Columns("A:H").Sort Key1:=WS.Columns("B"), Order1:=xlAscending, _
Key2:=WS.Columns(“A”), Order2:=xlAscending, Header:=x1No
for j=1 to 1000
if cells(1,1)="" then....code to delete row
next j
Next WS
End Sub

 

[DaveLansley]

Sub sort()
'Descending sort on A:H using columns B and A, all sheets in workbook
Dim WS As Worksheet
For Each WS In Worksheets
WS.Columns("A:H").Sort Key1:=WS.Columns("B"), Order1:=xlAscending, _
Key2:=WS.Columns(“A”), Order2:=xlAscending, Header:=x1No
for j=1 to 1000
if cells(1,1)="" then....code to delete row
next j
Next WS
End Sub

Thank you, but what would the code to delete the row be? Would Selection.EntireRow.Delete work?

 

[oldbrewer]

this macro deletes row 20

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 03/04/2019 by bob
'


'
Rows("20:20").Select
Selection.Delete Shift:=xlUp
End Sub

 

[oldbrewer]

noticed an error - should be if cells(j,1)=""..................

so the code is

rows(j).select
Selection.Delete Shift:=xlUp

 

[DaveLansley]

Thank you!

 

[oldbrewer]

if it doesnt work come back and post your code...we will fix it lol

 

[DaveLansley]

Hi

I'm afraid it's not working as I input around 50 rows of data (columns A-H all completed), ran the macro and it ended up deleting most of the rows, leaving me with only four. Here's the code:

Sub sort()
'Descending sort on A:H using columns B and A, all sheets in workbook
Dim WS As Worksheet
For Each WS In Worksheets
WS.Columns("A:H").sort Key1:=WS.Columns("B"), Order1:=xlAscending, _
Key2:=WS.Columns("A"), Order2:=xlAscending, Header:=x1Yes
For j = 1 To 100
If Cells(j, 1) = "" Then Rows(j).Select
Selection.Delete Shift:=xlUp
Next j



Next WS
End Sub

 

[oldbrewer]

maybe deleting rows affects the loop - so try

10 For j = 1 To 100
If Cells(j, 1) = "" Then Rows(j).Select
Selection.Delete Shift:=xlUp:goto 10
Next j