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

--
Message posted from http://www.ExcelForum.com

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 " skrev i melding
...
I have a complex spreadsheet which has blanks so therefore i can't use
advanced filter. There are too many columns to create a macro to use
autofilter so I was wondering if there is any vba code which will allow
me to filter all columns other than the current year. The data goes back
to 1996.

Cheers


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

"rbekka33 " skrev i melding
...

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

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

--
Message posted from http://www.ExcelForum.com

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

"rbekka33 " skrev i melding
...
The spreadsheet has 52 columns contain (in date format "2004 Jul" as a
custom number except for the Total columnns eg"2004 Total") data
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 for
the current year for this particular macro and the number of columns
increments every month-from 1 -12.

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

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

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

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


---
Message posted from http://www.ExcelForum.com/