Sorting Large Numbers of Rows, Hide Blanks

[Karl Burrows]

I am trying to clean up some older workbooks (Excel 2000) and am trying to
figure out how to sort current month sales. The workbook links to an Access
database (it pulls in the sales date, amount, etc.). The sales page then
looks at every row of the database tab and if the sale is in the current
month, it shows on the sales tab in a corresponding row. I then sort all
the sales in descending order (Z to A) to move the blanks to the bottom.

Here is what I would like to accomplish:

I want to find a way to populate the sales tab by only adding sales in the
current month instead of 'padding' the sales tab and creating 2500 rows of
formulas to look at the corresponding row in the database tab (hoping the
database tab doesn't exceed 2500 records).

Second, I would like to sort the data ascending and hide the blank rows
above. I have a Macro I have worked on for another workbook that unprotects
the workbook, unhides blank rows, sorts the data and then hides the blank
rows again and protects the worksheet again. The problem with this code is
it has a specifed range. I'm not sure how I can tell it to just look to the
last row with data and then hide every blank row that preceeds it.

Any help would be greatly appreciated!!!

Sub UnhideSortHideRows()
ActiveSheet.Unprotect Password:="xxx"
'
' UnhideRows Macro
'
Rows("11:312").Select
Selection.EntireRow.Hidden = False

' SortSummary Macro

Range("B10:K106").Select
Selection.Sort Key1:=Range("B11"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B113:K209").Select
Selection.Sort Key1:=Range("B114"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B216:K312").Select
Selection.Sort Key1:=Range("B217"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' HideBlankRows Macro

Const MYCOL As String = "B"
Const STARTROW As Long = 11
Dim rng As Range

Application.ScreenUpdating = False
With ActiveSheet
.Rows(STARTROW).Insert
.UsedRange
With Range(.Cells(STARTROW, MYCOL), .Cells(Rows.Count, _
MYCOL).End(xlUp).Offset(-1, 0))
.AutoFilter Field:=1, Criteria1:="=", _
Operator:=xlOr, Criteria2:="=0"
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter Field:=1
End With
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
ActiveSheet.Rows(STARTROW).Delete
Application.ScreenUpdating = True
Range("A1").Select
ActiveSheet.Protect Password:="xxxx"
End Sub

 

[NickHK]

Karl,
Could you to be more selective on the initial data pull from Access ? i.e.
.....WHERE MonthColumn=Current Month

NickHK

 

[Karl Burrows]

I'm not sure I follow you, but the sales page just has a formula to match
each row on the database link page. For example:

=IF(AND(Database!G1>=CurrentMonthStart,Database!G1<=CurrentMonthEnd),Databas
e!B1,"")
=IF(AND(Database!G2>=CurrentMonthStart,Database!G2<=CurrentMonthEnd),Databas
e!B2,"")
etc 2500 times...

The database page is just a query to populate the cells with product, date
and amount. Pretty simple import. No programming involved other than to
exclude old products no longer sold.

Thanks!