[RESOLVED] Sort Delete Blank Rows

[tome10]

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;

        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

[seenu_1st]

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?

[tome10]

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.

[seenu_1st]

this is what i said how to sort without show any changes in screen

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...

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?

[tome10]

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.

[seenu_1st]

what is the use of blank rows in between? can u show some images of ur needs?

[seenu_1st]

see this link may be useful for u.

[tome10]

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.

[seenu_1st]

oh! u just need to delete entire row?

Sheets(1).Range("a5").EntireRow.Delete

[seenu_1st]

anhn, good point in sorting function, thanks for the info.

[kaliman79912]

Just a quick note. correct me if Im wrong.

.Formula = "=IF(B6<>"""",B6,C6)"

Is not that the same as .Formula = "=IF($B6<>"""",$B6,IF($C6<>"""",$C6,""""))" ?

[tome10]

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?

        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.

        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

[seenu_1st]

no problem with this code, it works fine for me,

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

[tome10]

Where did Anhn's post go? I was going to try the Code.
It was something about Changing the J6

Key1:=Range("J6") to something like: 
Key1:=Range("Sheets("LD").Range("J6")

Anyone remember?

[seenu_1st]

i think it's like this

Key1:=Sheets("LD").Range("J6")