vba code to filter

[rbekka33]

I have a complex spreadsheet which has blanks so therefore i can't us
advanced filter. There are too many columns to create a macro to us
autofilter so I was wondering if there is any vba code which will allo
me to filter all columns other than the current year. The data goes bac
to 1996.

Cheer

 

[Harald Staff]

Hi

This will hide rows where col A date has another year than entered. Select
Cancel in the dialog to unhide all.

Sub test()
Dim L As Long, Yr As Long
L = 1
Yr = Val(InputBox("year to show:"))
If Yr = 0 Then
Application.ScreenUpdating = False
Do
L = L + 1
Rows(L).Hidden = False
Loop Until Cells(L, 1).Value = ""
Application.ScreenUpdating = True
Else
Application.ScreenUpdating = False
Do
L = L + 1
If Year(Cells(L, 1).Value) = Yr Then
Rows(L).Hidden = False
Else
Rows(L).Hidden = True
End If
Loop Until Cells(L, 1).Value = ""
Application.ScreenUpdating = True
End If
End Sub


HTH. Best wishes Harald

 

[rbekka33]

hi,

when i run this it stops at
If Year(Cells(L, 1).Value) = Yr Then

I type in the dialogue box

2004 Total

any ideas

 

[Harald Staff]

I type in the dialogue box
2004 Total
any ideas?

I said "where col A date has another year than entered". If the dates are
not in column A, or if your data is not dates but something else, then you
must either modify the code or tell us what and where your data is. "2004
total" is not a year (but my code should be able to extract 2004 from that
entry, so the problem is elsewhere).

Best wishes Harald

 

[rbekka33]

The spreadsheet has 52 columns contain (in date format "2004 Jul" as
custom number except for the Total columnns eg"2004 Total") dat
starting at column P. In the previous columns there is product info
including country regions etc. I only need to test for data that is fo
the current year for this particular macro and the number of column
increments every month-from 1 -12.

Why i need to do this is that once I have filtered the data, I the
need to match it against the source spreadsheet to determine brand ne
product codes as these rows will need to be imported differently fro
the ones that already exist. once imported the new codes then nee
custom formatting.

With the old codes i only bring across relevant columns. with the ne
codes i need to bring across everything.

I could I guess do this from a pivot table and hide the blanks and the
match the source product code column against the pivot table. I wa
hoping to be able not to have to create a pivot table as it run
slowly.

Any ideas or is more info needed?
Thanks for your patience

 

[Harald Staff]

My initial code just hid/unhid rows, I figured this might be for printing or
something. I don't really understand the data layout (like why multiple
total cells on each row ?), and hate to say I'm out of ideas on this.

Best wishes Harald