Ignoring blank cells when sorting data

[colmcg]

I posted earlier and got the following code to sort names across multiple sheets (thanks Pennysaver)

Sub SortDaysoftheWeek()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
With ws
.Activate
.Range("A6:G256").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Select
Next ws
End Sub

However I've hit a problem. I've extended the sort down to row 500 to allow for further names being added in the future. Now when I run the code all the blank cells appear at the top of the sorted data.

How do I adjust this code to only sort rows that contain a name in column A?

 

[zzjasonzz]

if you are only worried about blank cells after the data then you can change

.Range("A6:G256").Sort

to

.Range("A6:G" & .range("A1000").end(xlup).row).Sort

or

.Range("A6:G" & .range("A6).end(xldown).row).Sort

or alternatively you can just delete the blank rows at the top at the end with something like

for i = 6 to 200
   if .range("A" & i) = "" then
        .rows(i).delete
        i = i - 1
   else
      i = 200
  end if

next i

 

[Blade Hunter]

Change

.Range("A6:G256").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

to

.Range("A6:G" & Cells(Rows.Count,"G").End(xlUp).Row).Sort Key1:=Range("A6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

This will now grow with your data.

 

[Blade Hunter]

if you are only worried about blank cells after the data then you can change

.Range("A6:G256").Sort

to

.Range("A6:G" & .range("A1000").end(xlup).row).Sort

or

.Range("A6:G" & .range("A6).end(xldown).row).Sort

Cells(Rows.Count,"A").End(xlUp).Row is a better option than range("A1000").end(xlup).row as it is not limited to 1000 rows, it also grows with Excel (The new one supports over a million rows where as the old one was 65536)

 

[colmcg]

Blade Hunter & zzjasonzz.

Thanks for your help. I think End (xlUp) will be a better option because as you quite rightly say it will grow with my data.

 

[iliace]

I would suggest you use a named range that dynamically adjust to the amount of data, instead of having 500 hard-coded. So for your .Range("A6:G256") bit, instead have a name:

Name: SortData
Refers to: =OFFSET($A$6, 0,0, COUNTA($A:$A),7)

You might have to adjust for the number of cells counted (maybe -5 from the COUNTA() to skip any filled-in cells in column A above row 6). Now you can use Range("SortData") in place of your hard-coded cell reference.

 

[colmcg]

Help!

I've hit a snag with the sort using the above code.

Sheet called 'Names' contains a list of names in Column A. At the moment goes down to row 256 however this list is expanding all of the time.
I also have 7 further sheets called 'Sun, Mon, Tue, Wed, Thu, Fri & Sat'. Column A on each of these 7 sheets contains the formula =Names!A? When I run the above code, if I stipulate that the range is A6:G256 the sort code works perfectly.

However, to allow for additional names I have extended the formula in column A on sheets Sun - Sat down to row 506. As there are no names in sheet Names from row 257 to row 506 the formula in sheets Sun to Sat return 0. If I amend the code to sort range A6:G506 the zeros are included in the sort. The sort on the Names sheet works because there are blanks cells from row 257 to 506. Tried using - Cells(Rows.Count,"A").End(xlUp) but this still includes the zeros in the search.

Is there any way I can 'set' the sort range to only sort all 8 sheets down to the last entry in column A on sheet Names.

I hope this makes sense.

 

[chaosisbliss]

I've tried using the codes referenced above but can't get the sort to ignore any blank cells. Maybe someone could assist me with this issue. Any help would be much appreciated.

What I would like to do is Sort any data(ignoring blank cells) in columns A:G. The sort would be done with my data in column F.