Declare Variables

[gaz1959]

Hi,
I have a table with approx 44 headings and at present 7800 rows. The rows are increased by approx 30 each week with new data.

I have not declared any variables, please can you suggest what variables I should declare, and at what point in the code should the variables be mentioned.

Also where I have the code look to row 9999 (which gives me scope for the weekly added new rows, could I just make this say 99999 without causing any problems).

The code is just one of many, all the same except for the col that the 9999 is applied to, and sometimes that col has blank cells, hence the 9999.

Thanks.

Here's the code which achieves the result exactly as I need it to along with my notes:

Sub SEE_OnlySoldThisWeek()
'from a combo box choice stored in named range SUPP_Name to only show that supplier
'run from the MENU sheet

Sheets("ALLdetails").Visible = True

Sheets("ALLdetails").Select
ActiveSheet.Unprotect
Application.ScreenUpdating = False

'unhide all the columns in case any left from previous sorts
' cols only go to CL at present, D:D gives scope for more
Columns("A:DD").Select
    Selection.EntireColumn.Hidden = False
    
'remove any previous filters (must be filtered before otherwise this will bug it)
ActiveSheet.ShowAllData

    
'select the supplier full name (eg. 30 - LEWIS)col M,

    Range("M1").Select
' filter criteria & hide the arrows
    Selection.AutoFilter
        
    ActiveSheet.Range("$M$1:$M$9999").AutoFilter Field:=1, Criteria1:=Range("SUPP_Name").Value, VisibleDropDown:=False

' go to cell A1 to remove the selection of col I (must be a better way, perhaps it does not even need it)
'Range("A1").Select
'or maybe this
'ActiveWindow.ScrollRow = 2

' maybe this, col AR #44 is 2nd unprotected col ready for user input
Columns(44).Cells.SpecialCells(xlCellTypeVisible).Cells(2).Select

'=====================
'the sort and hide bit
'======================
'sort the whole table starting from PLU (H) to the end (BG) columns
'set sort criteria for col X, from row 2 to the last row, in desending order
Range("H2", Range("BG" & Rows.Count).End(xlUp).Address).Sort key1:=[X2], order1:=xlDescending, Header:=xlNo

'now hide the rows that have not sold this week <1(zero)
'look in col X from row 2 to end, then up to last used cell
' approx 30 rows added each week,at 7800 now, 9999 gives scope for a while
    Set rng = Range("X2", Range("X9999").End(xlUp))

        For Each cel In rng

        If cel.Value < 1 Then 'if cell is less than 1, ie none sold this week

            cel.EntireRow.Hidden = True  'hide the row

         'if greater than 1, ie some sold this week, leave it shown and go to next row
        End If

    Next cel 'keep going until all rows checked
    
 'hide columns to only show those needed for this report
 
ActiveSheet.Columns("K:W").Select
Selection.EntireColumn.Hidden = True
' CL is last col as BF-CI are only heading at present
ActiveSheet.Columns("Y:CL").Select
Selection.EntireColumn.Hidden = True
    
'scroll to the top
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollColumn = 1

ActiveSheet.Protect

'hide the menu sheet
Sheets("MENU").Visible = False

Application.ScreenUpdating = True
End Sub

 

[gaz1959]

Mark, got a run -time error 1004, Autofiter method of range class failed,

In the meantime I have put an IF statement in Col R so that if Col X sold amount = 0 "NONE", 1>= "SOME" and tried adding that to the filter. When run it hides all the rows.

Sub SEE_OnlySoldThisWeek789()
Dim cel As Range, Rng As Range, lr As Long

Sheets("ALLdetails").Visible = True
Sheets("ALLdetails").Select
ActiveSheet.Unprotect
Application.ScreenUpdating = False

lr = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row


Range("M1:M" & lr).AutoFilter Field:=1, Criteria1:=Range("SUPP_Name").Value, VisibleDropDown:=False
Range("R1:R" & lr).AutoFilter Field:=6, Criteria2:="SOME", VisibleDropDown:=False


' no problems here onwards to hide the needed columns

ActiveSheet.Columns("K:W").Select
Selection.EntireColumn.Hidden = True
' CL is last col as BF-CI are only heading at present
ActiveSheet.Columns("Y:CL").Select
Selection.EntireColumn.Hidden = True

'scroll to the top
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollColumn = 1

ActiveSheet.Protect

'hide the menu sheet
Sheets("MENU").Visible = False

Application.ScreenUpdating = True
End Sub

 

[MARK858]

Why do you have a field 6 when your range is only 1 column?

Range("R1:R" & lr).AutoFilter Field:=[COLOR="#FF0000"]6[/COLOR], Criteria2:="SOME", VisibleDropDown:=False

 

[gaz1959]

Why do you have a field 6 when your range is only 1 column?

Range("R1:R" & lr).AutoFilter Field:=[COLOR=#FF0000]6[/COLOR], Criteria2:="SOME", VisibleDropDown:=False

Mark,
Most likely because I'm clutching at straws!

I had tried it with AutoFilter Field:=1 but still got all the rows hidden.

Searching around it does look as if filtering on 2 columns is very difficult, (very easy to do manually though so can't see why), your code for instance got that run - time error.

Here is my long-winded work around which I might have to resort to:

Once the first sort has been done, copy the result to another sheet and do the second filter there, then display that result.

 

[MARK858]

I can't envisage what is different on your sheet to my sheet's setup that is causing the error on the code I posted as I tested it again last night and had no error.

I don't think I will get to the bottom of it without seeing your sheet.

 

[gaz1959]

Mark,
Apologies for a late reply, thank you very much for all your help, and of course all the others who pitched in. In my limited knowledge I could not understand why your code errored. You'll be glad to know that I did not use my long-winded way, but got it working using the code below.
I'm sure you're see me pop-up again for advice, so thanks again all. Problem solved.

With ActiveSheet 'active sheet is now the ALLdetails sheet


.AutoFilterMode = False

With .Range("M10:R10")
'start on colM as it is the first col to filter to the last col, in this case colR

.AutoFilter

'1st field 1 is col M
.AutoFilter Field:=1, Criteria1:=Range("SUPP_Name").Value, VisibleDropDown:=False

'2nd field 6 is col R, NONE will show those with no sales,
.AutoFilter Field:=6, Criteria1:="NONE", VisibleDropDown:=False

'SOME those with sales
'.AutoFilter Field:=6, Criteria1:="SOME", VisibleDropDown:=False


'++++++++++
'now sort the result
'++++++++++
'sort the whole table starting from PLU (H) to the end (CL) columns
'set sort criteria for col X, from row 11 to the last row, in desending order
Range("H11", Range("BG" & Rows.Count).End(xlUp).Address).Sort key1:=[X11], order1:=xlDescending, Header:=xlNo