Sort Excel worksheet from 3rd rows down?? 4

[qajussi]

Hi!

I want to sort the worksheets by certain column I choose.
I can do the simple sort but I am having problem figuring out how to sort from 3rd row down.
first 2 rows have some header info and titles.
I also don't know how many rows I will have.
But I know all the worksheets have data from 3rd row.

1) Can I select 3rd row to whatever rows I have and sort??
Does this make any sense??
Thanks much.

 

[Zathras]

You can use the Intersect method. Something like this:
[tt]
Intersect(ActiveSheet.UsedRange, Range("3:65536")).Sort _
Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range("B3") _
, Order2:=xlAscending, Key3:=Range("C3") _
, Order3:=xlAscending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom
[/tt]

 

[PHV]

Straight from the macro recorder:
Range("A3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=...

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

 

[firefytr]

Using the xlLastCell is unreliable, and hard coding 65536 will not work in future versions of Excel. It's best to use the worksheet itself to get the last row of data..

Range("A2", Cells(rows.count, 1).end(xlup)).Sort key1:=Range("A3"), order1:=xlascending, header:=xlyes

You *may* have a two row header, but VBA only cares about that one-liner.

HTH

-----------
Regards,
Zack Barresse

 

[vladk]

firefytr,

In your example, you didn't specify to what objects you meant to apply your Cells, rows, and Range properties.

vladk

 

[firefytr]

I'm sorry vladk, I'm not following. You mean a parent object?

-----------
Regards,
Zack Barresse

 

[vladk]

Hi firefytr,

I mean the classes which members are Cells, Rows, and Range. For example, the Cells collection is in fact is either Application.Cells or Range.Cells or Worksheet.Cells. I think, without such a full specification the code could sometimes go to the wrong direction. I learned it by chance when coded Excel from VB6 and when I did not fully qualify these objects, Excel process remained in the memory after the program end, even if I destroyed all EXCEL objects in the code.

vladk

 

[PHV]

vladk, it is standard behaviour with automation.
firefytr's code running inside excel will not exhibit this.

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

 

[vladk]

PHV,

I know this, but since class was not specified, how would you say where code goes?

vladk

 

[firefytr]

vladk, yes, I agree with you. I generally tell people to explicitly reference their objects (e.g. worksheets, workbooks, applications, all where necessary). I was more or less being lazy I guess. You could adapt such as ..

Dim wb as workbook, ws as worksheet
Set wb = Thisworkbook
Set ws = wb.sheets("Sheet1")
ws.Range("A2", ws.Cells(ws.rows.count, 1).end(xlup)).Sort key1:=ws.Range("A3"), order1:=xlascending, header:=xlyes
set ws = nothing
set wb = nothing

Without referencing the parent objects Excel will assume it is for the activeworkbook on the active sheet, which can lead to problems if the user isn't where they need to be. Thanks for keeping me straight vladk.

-----------
Regards,
Zack Barresse

 

[vladk]

Hi firefytr,

I also sometimes don't specify all the parent objects...
BYW, I looked at the EXCEL 2007 beta today: it has something like 1,000,000 rows at the worksheet, don't remember the exact number. So your way of referencing number of rows on the worksheet is indeed very smart! I would put it in workbook_open event so this count could be available as a global variable across the board.

vladk

 

[qajussi]

THANKS everyone for all your help.

Can I ask one more question?

I created a menubar and put a button.
I am adding this menubar when I open the spreadsheet and delete it when I close it.

A while I was testing it I forgot to deleted them.
So I made 5 menubars.
I deleted the button control. but the menubars didn't go away. So I have 5 blank menubars (5 lines).
How do i delete these ?
Thanks much.


Sub AddNewMB()
Dim MBar As CommandBar, MBarCtl As CommandBarControl
Dim MBarPopup As CommandBarPopup, MBarSubCtl as CommandBarControl
On Error GoTo AddNewMB_Err

' Create a new menu bar and dock it on the left.
Set MBar = Application.CommandBars.("Worksheet Menu Bar")
' Make the menu bar visible.
MBar.Visible = True
' Prevent users from undocking the menu bar.
MBar.Protection = msoBarNoMove

' Create a popup control on the bar and set its caption.
'Set MBarCtl = MBar.Controls.Add(Type:=msoControlPopup) <=== I used a few times but don't need this anymore.
'MBarCtl.Caption = "Report Tracking"

' Create 2 controls on the Display popup and set some properties.
Set MBarSubCtl = MBarCtl.Controls.Add(Type:=msoControlButton)
With MBarSubCtl
.Style = msoButtonIconAndCaption
.Caption = "Open Report"
.FaceId = 59
.OnAction = "Userform2
.Parameter = 1
.BeginGroup = True
End With
End Sub

Sub Uninstall()
dim cbc as Object
for each cbc in Application.commandbars(Worksheet Menu Bar").controls
if cbc.caption = "Open Report" then
cbc.delete
exit for
end if
next cbc

End Sub

 

[qajussi]

I just needed a button on the menubar or toolbar.

This code does the job.

I just want to remove those blanks.


Another question.

When I open two different excel spreadsheets, I get that menubar on both spreadsheets. Why is that?
Even though the other one doesn't have this code in their workbook??

Thanks millions.

 

[firefytr]

Maybe take a look at this ...

Sub AddNewMB()
    Dim MBar As CommandBar, MBarCtl As CommandBarControl
    Dim MBarPopup As CommandBarPopup, MBarSubCtl As CommandBarControl
    On Error GoTo AddNewMB_Err
    Call DelNewMB
    Set MBar = Application.CommandBars("Worksheet Menu Bar")
    MBar.Protection = msoBarNoMove
    Set MBarSubCtl = MBarCtl.Controls.Add(Type:=msoControlButton)
    With MBarSubCtl
        .Style = msoButtonIconAndCaption
        .Caption = "Open Report"
        .FaceId = 59
        .OnAction = "Userform2"
        .Parameter = 1
        .BeginGroup = True
    End With
End Sub

Sub DelNewMB()
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("Open Report").Delete
End Sub

(untested)

-----------
Regards,
Zack Barresse