Solved

Referencing data from a table, in excel 2003, Macro

Posted on 2011-03-06
22
323 Views
Last Modified: 2012-05-11
MACRO:  Looking to sort by Project (Ascending Order) then by Date (Most recent first) - I wrote it backwards in the excel file.  & a 2nd macro to clear all the headers.  The ISSUE begins when:

- I add columns
- Add a row above the table

So, I want to be able to reference the data section even when I add a row just below the header row.

Thanks,
JP Macro---Sort-by-to-items---point.xls
0
Comment
Question by:easycapital
  • 10
  • 6
  • 6
22 Comments
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35048199
Hi there,

Perhaps you could use something like this...

Sub SortMyData()
    Dim WS As Worksheet, rSort As Range
    Dim iRow As Long, iCol As Long
    Set WS = Workbooks("Macro---Sort-by-to-items---point.xls").Sheets("Sheet1")
    iCol = WS.Cells(7, WS.Columns.Count).End(xlToLeft).Column
    With WS.Range("B7", WS.Cells(WS.Rows.Count, iCol))
        iRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    Set rSort = WS.Range("B7", WS.Cells(iRow, iCol))
    rSort.Sort Key1:=rSort(1, 1), Order1:=xlAscending, Key2:=rSort(1, 2), Order2:=xlAscending, Header:=xlYes
End Sub

Open in new window


This assumes you WILL have a header row.  If you will not always have a header row, define for us how you will know logically that there is a header row or not (i.e. the first row is bold), etc.  It also assumes that your data range will ALWAYS start on B7.  If this is not the case, please let us know, and how we will know where the data is on the worksheet.

HTH
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35048437
Try this one. The macros are included in the attached file.
Assuming your table is 2 columns with headers named "Date" and "Project"
You can insert or delete rows above the table as you like.
Curt
Sub Sort_Table()

    Dim i As Long
    For i = 1 To xlLastRow
        If Cells(i, 2) = "Date" And Cells(i, 3) = "Project" Then
            ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
            Range("B" & i & ":C" & i).Select
            Range(Selection, Selection.End(xlDown)).Select
            ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C" & i + 1 & ":C" & xlLastRow) _
                                                                  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B" & i + 1 & ":B" & xlLastRow) _
                                                                  , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets("Sheet1").Sort
                .SetRange Range("B" & i & ":C" & xlLastRow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
    Next
End Sub
Function xlLastRow(Optional WorksheetName As String) As Long
     
    'Check for optional worksheetname else use activesheet
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    
     '    find the last populated row in a worksheet
    With Worksheets(WorksheetName)
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row
    End With
     
End Function

Open in new window

Macro---Sort-by-to-items---point.xls
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35048492
I don't know if that's the best way to go about it, epaclm.  There is no need to select anything, and will reduce the efficiency of your code.  As I stated to the OP, if what appears to be a constant (i.e. starting in cell B7) isn't necessarily a constant, there are other, more efficient ways, of going about it.
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35049114
firefytr,

I thought it was obvious that B7 isn't constant! See request from Author:
"- I add columns
- Add a row above the table
"
You're right about the select statements of course.

JP,

I have not solved the problem of adding columns to the left of the table but my code works for inserting rows above the table (even if it takes a couple of extra ms to run for a large table!).
You can remove line 7 and 8 of the code if you like
            Range("B" & i & ":C" & i).Select
            Range(Selection, Selection.End(xlDown)).Select
They're not required but they do not change how this macro works whether they are there or not.

Curt
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35049199
It's not obvious to me.  When the OP stated they wanted to add a row above the table, to me that means leaving the header in place.  If this is the case, then it's not so obvious now, is it?  Adding columns is quite accounted for in my code.

Zack
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35049714
Zack,

Apologies, You're probably right! I should have read the next line:
"So, I want to be able to reference the data section even when I add a row just below the header row."

I think your interpretation is the correct one and your code works as long as the data always starts in B7.

Curt
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35049997
I'm hoping we will get some details from the OP as to clarify that issue!  :)

Zack
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35053117
Maybe this code based on Zack's solution can be used if B7 is not fixed.
Instead of using B7 as a constant I'm now using a defined name for the top left header. The table can now be positioned anywhere in the sheet.

Curt
Sub SortMyData()
    Dim WS As Worksheet, rSort As Range, rStart As Range
    Dim iRow As Long, iCol As Long

    Set WS = ThisWorkbook.Sheets("Sheet1")
    Set rStart = WS.Range("tablestart")
    iCol = WS.Cells(rStart.Row, WS.Columns.Count).End(xlToLeft).Column
    With WS.Range(rStart, WS.Cells(WS.Rows.Count, iCol))
        iRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    Set rSort = WS.Range(rStart, WS.Cells(iRow, iCol))
    rSort.Sort Key1:=rSort(1, 2), Order1:=xlAscending, Key2:=rSort(1, 1), Order2:=xlDescending, Header:=xlYes
End Sub

Open in new window

Macro---Sort-by-to-items---point.xls
0
 

Author Comment

by:easycapital
ID: 35061953
STILL NEEDING:
Curt I was reading your code and from what I am able to understand, it counts columns in the table; could it go off the table header instead? This way if I add a column in between the date and the project, the macro will continue to work based on the project sorting?

One last thing, one one of the first macros I would like to set in here prior to closing the question would be to "Clear or unselect" all the filters; let's say that I filter by project B, then this macro would clear that filter.  

OTHER COMMENTS:
The first and last solution seem pretty compact - nice.  The last solution presented by Curt makes the table start a defined name - so the table can begin anywhere in the sheet - very nice again.

The reason for this question is to be able to add different filter requests through macros and how have to worry if a column is added or a row.

Thank you very much,
JP
 
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35062142
Hi,

You still haven't really answered the question about the top-left cell of your table.  Will it always be there?  More information would give you a more complete solution.

If your top-left cell will always be B7, you can use this to always sort by Date and Project, so long as they are there (no error handling)...

Sub SortMyData()
    Dim WS As Worksheet, rSort As Range
    Dim iRow As Long, iCol As Long
    Dim rDate As Range, rProject As Range
    Set WS = Workbooks("Macro---Sort-by-to-items---point.xls").Sheets("Sheet1")
    iCol = WS.Cells(7, WS.Columns.Count).End(xlToLeft).Column
    With WS.Range("B7", WS.Cells(WS.Rows.Count, iCol))
        iRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    Set rSort = WS.Range("B7", WS.Cells(iRow, iCol))
    Set rDate = WS.Range("7:7").Find(What:="Date", LookAt:=xlWhole, MatchCase:=True)
    Set rProject = WS.Range("7:7").Find(What:="Project", LookAt:=xlWhole, MatchCase:=True)
    rSort.Sort Key1:=rDate, Order1:=xlAscending, Key2:=rProject, Order2:=xlAscending, Header:=xlYes
End Sub

Open in new window


If you wanted error handling for the above, it would be ...

Sub SortMyData()
    Dim WS As Worksheet, rSort As Range
    Dim iRow As Long, iCol As Long
    Dim rDate As Range, rProject As Range
    Set WS = Workbooks("Macro---Sort-by-to-items---point.xls").Sheets("Sheet1")
    iCol = WS.Cells(7, WS.Columns.Count).End(xlToLeft).Column
    With WS.Range("B7", WS.Cells(WS.Rows.Count, iCol))
        iRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    Set rSort = WS.Range("B7", WS.Cells(iRow, iCol))
    Set rDate = WS.Range("7:7").Find(What:="Date", LookAt:=xlWhole, MatchCase:=True)
    Set rProject = WS.Range("7:7").Find(What:="Project", LookAt:=xlWhole, MatchCase:=True)
    If Not rDate Is Nothing Then
        If Not rProject Is Nothing Then
            rSort.Sort Key1:=rDate, Order1:=xlAscending, Key2:=rProject, Order2:=xlAscending, Header:=xlYes
        Else
            rSort.Sort Key1:=rDate, Order1:=xlAscending, Header:=xlYes
        End If
    Else
        rSort.Sort Key1:=rProject, Order1:=xlAscending, Header:=xlYes
    End If
End Sub

Open in new window


If this was the ONLY data on your sheet, and you wanted to hook the top-left cell, but didn't know if it was going to be B7 or not, you could use...

Sub SortMyData()
    Dim WS As Worksheet, rSort As Range
    Dim iRow As Long, iCol As Long
    Dim rDate As Range, rProject As Range
    Dim rStart As Range, rRow As Range
    Set WS = Workbooks("Macro---Sort-by-to-items---point.xls").Sheets("Sheet1")
    Set rStart = WS.UsedRange(1, 1)
    Set rRow = rStart.EntireRow
    iCol = WS.Cells(rRow.Row, WS.Columns.Count).End(xlToLeft).Column
    With WS.Range(rStart, WS.Cells(WS.Rows.Count, iCol))
        iRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    Set rSort = WS.Range(rStart, WS.Cells(iRow, iCol))
    Set rDate = WS.Rows(rRow.Row).Find(What:="Date", LookAt:=xlWhole, MatchCase:=True)
    Set rProject = WS.Rows(rRow.Row).Find(What:="Project", LookAt:=xlWhole, MatchCase:=True)
    If Not rDate Is Nothing Then
        If Not rProject Is Nothing Then
            rSort.Sort Key1:=rDate, Order1:=xlAscending, Key2:=rProject, Order2:=xlAscending, Header:=xlYes
        Else
            rSort.Sort Key1:=rDate, Order1:=xlAscending, Header:=xlYes
        End If
    Else
        rSort.Sort Key1:=rProject, Order1:=xlAscending, Header:=xlYes
    End If
End Sub

Open in new window


BUT, if you had other data besides that, you would need to specify what logic you would use to find the table.

Zack
0
 

Author Comment

by:easycapital
ID: 35062192
Hi Zack,

The closest solution would be one you provide in the middle.  The top left cell in the table my change - because if rows are added above the table, then the top left cell will shift down.  And I might add columns later on to the left.  This macro will be the pillar to a whole bunch of filtering that will reference the table, so I want to be certain that I can add columns and rows where necessary.  

My previous entry still remains open.

Thanks,
JP
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35062270
could it go off the table header instead? This way if I add a column in between the date and the project, the macro will continue to work based on the project sorting?
Address in all 3 of the above routines.

One last thing, one one of the first macros I would like to set in here prior to closing the question would be to "Clear or unselect" all the filters; let's say that I filter by project B, then this macro would clear that filter.
Will hit this in a second...

Since you're going to be inserting rows/columns above and to the left of this table, the named range is probably the way you want to go.  This will do as the above does in keeping the Date and Project columns dynamic (so long as the text is the same in whichever column header it's found in), plus it integrates the named range solution Curt coded...

Sub SortMyData()

    Dim WS As Worksheet, rSort As Range
    Dim rStart As Range, rRow As Range
    Dim iRow As Long, iCol As Long
    Dim rDate As Range, rProject As Range
    
    Set WS = ThisWorkbook.Sheets("Sheet1")
    Set rStart = WS.Range("TableStart")
    Set rRow = rStart.EntireRow
    
    iCol = WS.Cells(rStart.Row, WS.Columns.Count).End(xlToLeft).Column
    With WS.Range(rStart, WS.Cells(WS.Rows.Count, iCol))
        iRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    
    Set rSort = WS.Range(rStart, WS.Cells(iRow, iCol))
    Set rDate = WS.Rows(rRow.Row).Find(What:="Date", LookAt:=xlWhole, MatchCase:=True)
    Set rProject = WS.Rows(rRow.Row).Find(What:="Project", LookAt:=xlWhole, MatchCase:=True)
    
    If Not rDate Is Nothing Then
        If Not rProject Is Nothing Then
            rSort.Sort Key1:=rDate, Order1:=xlAscending, Key2:=rProject, Order2:=xlAscending, Header:=xlYes
        Else
            rSort.Sort Key1:=rDate, Order1:=xlAscending, Header:=xlYes
        End If
    Else
        rSort.Sort Key1:=rProject, Order1:=xlAscending, Header:=xlYes
    End If
    
End Sub

Open in new window


To clear the autofilter all you need is ...

Sub ClearFilters()
    ActiveSheet.AutoFilterMode = False
End Sub

Open in new window


Zack
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35062299
In fact, this might be a little more well-rounded of a sort routine, if various column headers are or aren't found..

Sub SortMyData()

    Dim WS As Worksheet, rSort As Range
    Dim rStart As Range, rRow As Range
    Dim iRow As Long, iCol As Long
    Dim rDate As Range, rProject As Range

    Set WS = ThisWorkbook.Sheets("Sheet1")
    Set rStart = WS.Range("TableStart")
    Set rRow = rStart.EntireRow

    iCol = WS.Cells(rStart.Row, WS.Columns.Count).End(xlToLeft).Column
    With WS.Range(rStart, WS.Cells(WS.Rows.Count, iCol))
        iRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With

    Set rSort = WS.Range(rStart, WS.Cells(iRow, iCol))
    Set rDate = WS.Rows(rRow.Row).Find(What:="Date", LookAt:=xlWhole, MatchCase:=True)
    Set rProject = WS.Rows(rRow.Row).Find(What:="Project", LookAt:=xlWhole, MatchCase:=True)

    If Not rDate Is Nothing And Not rProject Is Nothing Then
        rSort.Sort Key1:=rDate, Order1:=xlAscending, Key2:=rProject, Order2:=xlAscending, Header:=xlYes
    ElseIf Not rDate Is Nothing Then
        rSort.Sort Key1:=rDate, Order1:=xlAscending, Header:=xlYes
    ElseIf Not rProject Is Nothing Then
        rSort.Sort Key1:=rProject, Order1:=xlAscending, Header:=xlYes
    ElseIf Not rSort Is Nothing Then
        'sort however else you want
    End If

End Sub

Open in new window


Zack
0
 

Author Comment

by:easycapital
ID: 35062474
Hi Zack,

Would you mind putting your last solution in a file?  I do not seem to be setting up right.

Thanks,
JP
0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 250 total points
ID: 35062562
Sure, no problem.

Zack Test-for-JP-at-EE.xls
0
 
LVL 18

Assisted Solution

by:Curt Lindstrom
Curt Lindstrom earned 250 total points
ID: 35062831
Hi JP,

A slightly different approach.
The macro does not rely on any names at all except for the defined name "tablestart"
It will sort the table with the last column ascending and the first column descending.

Curt
Sub SortMyData()
    Dim WS As Worksheet, rSort As Range, rStart As Range
    Dim iRow As Long, iCol As Long, iFirstCol As Long

    Set WS = ThisWorkbook.Sheets("Sheet1")
    Set rStart = WS.Range("tablestart")
    iCol = WS.Cells(rStart.Row, WS.Columns.Count).End(xlToLeft).Column 'Last column of table
    iFirstCol = rStart.Column
    With WS.Range(rStart, WS.Cells(WS.Rows.Count, iCol))
        iRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    Set rSort = WS.Range(rStart, WS.Cells(iRow, iCol))
    rSort.Sort Key1:=rSort(1, iCol - iFirstCol + 1), Order1:=xlAscending, Key2:=rSort(1, 1), Order2:=xlDescending, Header:=xlYes
End Sub

Open in new window

Macro---Sort-by-to-items---point.xls
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35063034
However, if you always want to sort on "Project" as the first sort you would always have to keep it at the last column unless you modify my last macro.

In reality I think you would always keep your 2 sort columns in the first two columns of your table so iit's probably better to keep the sorting criteria line like this:
   rSort.Sort Key1:=rSort(1, 2), Order1:=xlAscending, Key2:=rSort(1, 1), Order2:=xlDescending, Header:=xlYes

That means your table will always start with the "Date" followed by "Project" columns. Sorting Project ascending and Date descending.

Curt
0
 

Author Comment

by:easycapital
ID: 35072134
Guys tremendous input on this question.  I will be performing many sorting and filtering through macros; in excel 2007 it is relatively easy to achieve this.  It was the fact that I could not reference to a Table that was giving me a real difficult time to figure out.

Let me take a look at how I addressed in 2007 and see if I can redirect this question in order to achieve a long lasting approach for my task.

Thanks,
JP
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35072143
Is this going to be performed on an actual Table?  Or just a table of data?  If an actual Table, we can just reference the Table name if you know it.

Zack
0
 

Author Comment

by:easycapital
ID: 35184333
Can one use Table names in 2003?

JP
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35184385
Sure, as the solutions already posted can handle that.  I was referring to an actual Table, which isn't the same thing in previous versions.  In 2007 and beyond, you'd find it under the Insert tab, right next to Pivot Table (Insert | Table).  A Table has it's own properties you can easily latch onto via code.

Do none of the solutions provided work for you?  Or are you looking for some additional functionality?

Zack
0
 

Author Closing Comment

by:easycapital
ID: 35262751
Thank you for all the feedback!
JP
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now