Sort data in dynamic range by header double-click?

[blackduck]

Sort a data range by double-clicking header cell" FAQ707-4886. This code by VBAJedi is fantastic and has worked well in all of my other tables.

I want to ignore blank rows when I sort by the No. field as there are no corresponding records in those rows yet.

My worksheet is:
D E F G
No. Name Text Date
(vlookup) (data val list) (data entry) (data entry)

Eg. the table has 30 rows and 20 rows have values. When I double-click on column D header, I get 10 blank rows then the numbers are sorted in order at the bottom half of the table.

I tried to make "dataArea" a dynamic range using offset but couldnt get that to work.
Any ideas on how to get around this?

thanks

 

[SkipVought]

hi,

"I want to ignore blank rows when I sort ..."

You should NEVER have blank rows in a table. It is a PLAGUE, it is not good design and it is not a best & accepted practice.

Short of that, define the entire range as the sort object.

Skip,

_[red][/red]

 

[blackduck]

Hi Skip

I have set the worksheet to look a bit like a web page in that it has header and footer information/graphics.
The table is within this and the user puts some rows in. When they start a row they fill in all cells so there are no blank cells in that row.

I have the entire range as the sort object. My D cells have the formula =IF(E22="","",VLOOKUP(E22,SiteList,4,FALSE))

Appreciate input on not having blank rows and dont want to have bad design. (Skip -usually read your replies first in threads) Any other ideas on how I could go about it and get the same sort of result?

thanks
Ke

 

[SkipVought]

Turn on your macro recorder and SORT.

Turn off your macro recorder and observe the recorded code.

Post back with your code if your need help.

Skip,

_[red][/red]

 

[blackduck]

Hi Skip, this is the code I am using - written by VBAJedi in faq. I dont understand everything it does but im trying.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim MyTarget As Range, x As Variant
Dim shre As Worksheet

Set shre = Worksheets("Planner")
Set MyTarget = Intersect(Target.Cells(1, 1), shre.Range("cpHeaderArea"))
If Not MyTarget Is Nothing Then
If SortArr(0) < 3 Then ' There is room for another criteria
x = SetArr(MyTarget.Column)
Select Case SortArr(0)
Case 1
shre.Range("cpDataArea").Sort Key1:=Cells(1, SortArr(1)), Order1:=xlAscending, _
Orientation:=xlTopToBottom
Case 2
shre.Range("cpDataArea").Sort Key1:=Cells(1, SortArr(1)), Order1:=xlAscending, _
Key2:=Cells(1, SortArr(2)), Order2:=xlAscending, _
Orientation:=xlTopToBottom
Case 3
shre.Range("cpDataArea").Sort Key1:=Cells(1, SortArr(1)), Order1:=xlAscending, _
Key2:=Cells(1, SortArr(2)), Order2:=xlAscending, _
Key3:=Cells(1, SortArr(3)), Order3:=xlAscending, _
Orientation:=xlTopToBottom
Case Else ' Defaults to sort on first column
shre.Range("cpDataArea").Sort Key1:=Cells(1, 1), Order1:=xlAscending, _
Orientation:=xlTopToBottom
End Select
Else
MsgBox "Only 3 sort criteria available. Press refresh to sort again."
End If
Cancel = True ' Cancels default double-click behavior
End If
End Sub

Function SetArr(SortByCol)
Dim x As Integer, Flag As Boolean
Flag = False
For x = 1 To 3
If SortArr(x) = 0 Then
SortArr(x) = SortByCol
SortArr(0) = x ' Set criteria count
Flag = True
Exit For
End If
Next x
SetArr = Flag
End Function

 

[PHV]

You may try to change all:
shre.Range("cpDataArea").Sort
with:
shre.Range("cpDataArea")[!].Cells(1, 1)[/!].Sort

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[blackduck]

Thanks PH, but it didnt work. I still got the blank ones and it also mixed the header in with it.

I know its tacky, but if I change my formula to.
=IF(E28="","z",VLOOKUP(E28,SiteList,4,FALSE))

I then added conditional formating so if formula =z then the font would be white and you dont see it.

This gave the next prob cause I have 'stripes' already. ie conditional formatting with, if formula =MOD(ROW(),2)=1 grey pattern. (result is alternate grey and white lines)

?

 

[PHV]

it also mixed the header in with it
change all:
shre.Range("cpDataArea").Sort ...
with:
shre.Range("cpDataArea").Cells(1, 1).Sort Header:=xlYes ...

If you stick to conditional formatting then:
if formula1 =AND(MOD(ROW();2)=1;D28="z") grey font
if formula2 =AND(MOD(ROW();2)=0;D28="z") white font

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry for bad locale ...
if formula1 =AND(MOD(ROW(),2)=1,D28="z") grey font
if formula2 =AND(MOD(ROW(),2)=0,D28="z") white font

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[blackduck]

Hi PHV

I highlighted the whole range, conditional formatting, put both formulas in condition 1 and condition 2, made background pattern for both grey, and selected grey font for cond1 and white font for cond1.

Result was all rows with numbers in column D became white with black font.
Rows with z in column D - grey pattern got grey font -good
but white columns kept the black font z.

almost there.