[RESOLVED] Sort Delete Blank Rows
I have this code that sorts my Used Range. It works, but there is a couple of annoying things I wish to correct.
1) How do you set the Page to sort without the Page being the active sheet? I discovered the sort was Erroring, and I couldn't figure out what was wrong. Apparently the sheet has to be active in order to do a sort. So as a quick fix I just activated the sheet then ran the code and then re-activate the other sheet.
2) Occasionally, I have a code that runs and moves one or two rows to another sheet which leaves Blank Rows. The Sort command strangely leaves the Blank Rows where they are, right in the middle of my Used Range. It just sorts around them. So I either need to figure out how to move the blank Rows down out of the Used Range, or just delete them.
Thanks for your help. Here's the code;
Code:
Sub PrSort()
Sheets("LD").Activate
Dim srtCell As Range
Sheets("LD").Range("J6:J62").ClearContents
For Each srtCell In Sheets("LD").Range("J6:J62")
If srtCell.Offset(0, -8) <> "" Then
srtCell.Value = srtCell.Offset(0, -8).Value
Else
If srtCell.Offset(0, -7) <> "" Then
srtCell.Value = srtCell.Offset(0, -7).Value
End If
End If
Next
Sheets("LD").Range("A6:J62").Sort Key1:=Range("J6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Re: Sort Delete Blank Rows
Q1, yes i think need to select that sheet to sort, but u can use Application.ScreenUpdating = False b4 sort, so screen shows no change of sheet selection, after sort u can use Application.ScreenUpdating = True.
Q2, what hapens if u delete the blank row?
Re: Sort Delete Blank Rows
You can't just Dim the sheet somehow and sort it?
I have to manually unprotect the sheet and delete the Row. Then the Sort works as it should.
It sorts by a Column of Dates. Strange how it ignores the Blank Row.
Re: Sort Delete Blank Rows
this is what i said how to sort without show any changes in screen
Code:
Application.ScreenUpdating = False
Sheets(2).Select
Sheets(2).Range("A1:A20").Sort _
Key1:=Worksheets(2).Range("A1"), order1:=xlAscending
Sheets(1).Select
Application.ScreenUpdating = True
u can protect / unprotect sheets like this...
Code:
Sheets(2).Unprotect Password:="password"
Sheets(2).Protect Password:="password"
edit: I think sort doesn't ignore blank rows, then there is no logic to sort, why dont u insert blank row where u need after sort?
Re: Sort Delete Blank Rows
I know about the screen updating trick, and I know the Disable Calculations. Which since messing with the Calculations disable code a couple days ago my whole excel program is autocalc off. So when I get it back on, the auto calc code will never go in my code again.
Are you sure you can't sort a sheet irregardless of it being selected?
Also, I need a way to get rid of the Blank Rows.
Re: Sort Delete Blank Rows
what is the use of blank rows in between? can u show some images of ur needs?
Re: Sort Delete Blank Rows
see this link may be useful for u.
Re: Sort Delete Blank Rows
lol there is no use in Blank Rows in the middle of my used range i'm trying to get rid of them. I have a Code that occasionally runs and copies a row and inserts in another sheet. It then deletes content out of the main sheet which leaves a blank row. Maybe I need to change the clear contents to entire row delete.
Re: Sort Delete Blank Rows
oh! u just need to delete entire row?
Code:
Sheets(1).Range("a5").EntireRow.Delete
Re: Sort Delete Blank Rows
anhn, good point in sorting function, thanks for the info.
Re: Sort Delete Blank Rows
Just a quick note. correct me if Im wrong.
vb Code:
.Formula = "=IF(B6<>"""",B6,C6)"
Is not that the same as .Formula = "=IF($B6<>"""",$B6,IF($C6<>"""",$C6,""""))" ?
Re: Sort Delete Blank Rows
I have a problem, when I change the Line .ClearContents to .Delete, it eventually starts deleting my Header rows which are Rows 1-5. Why is that?
Code:
Sub Complete()
Sheets("LD").Unprotect Password:="thomas"
For Each mycell In Sheets("LD").Range("C6:C62")
If mycell.Value = "COMPLETE" Then
mycell.EntireRow.Copy
Sheets("Hist").Rows("5:5").Insert Shift:=xlDown
Sheets("Hist").Range("B5") = Sheets("Hist").Range("A1").Value ' just sets the current date
End If
Next
For Each mycell In Sheets("LD").Range("C6:C62")
If mycell.Value = "COMPLETE" Then
mycell.EntireRow.ClearContents ' When I change this to .Delete it starts deleting my header rows which are 1-5 why is that?
End If
Next
Sheets("Hist").Range("E5:H12").Validation.Delete
Sheets("LD").Protect Password:="thomas"
End Sub
Nevermind, I was messing with the code yesterday and forgot I added this to the sort Code.
Code:
Dim i As Long
'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
End With
'.ScreenUpdating = False
'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
With Application
.Calculation = xlCalculationAutomatic
End With
Re: Sort Delete Blank Rows
no problem with this code, it works fine for me,
Code:
For Each mycell In Sheets(1).Range("C6:C62")
If mycell.Value = "COMPLETE" Then
mycell.EntireRow.Copy
Sheets(2).Rows("5:5").Insert Shift:=xlDown
Sheets(2).Range("B5") = Sheets(2).Range("A1").Value
End If
Next
For Each mycell In Sheets(1).Range("C6:C62")
If mycell.Value = "COMPLETE" Then
mycell.EntireRow.Delete
End If
Next
Re: Sort Delete Blank Rows
Where did Anhn's post go? I was going to try the Code.
It was something about Changing the J6
Code:
Key1:=Range("J6") to something like:
Key1:=Range("Sheets("LD").Range("J6")
Anyone remember?
Re: Sort Delete Blank Rows
i think it's like this
Code:
Key1:=Sheets("LD").Range("J6")
