PDA

View Full Version : Filter and hide macro help



chrishill123
03-14-2008, 08:28 AM
Hi all

I am working on a excel sheet to help my department plan purchassing for up coming jobs.

I have created the database sheet with the current jobs and requirements. What I have is a list of parts in the rows, and the jobs in the columns.

The company has three types of job Customer, Public lighting and MEA, and job numbers are prefixed CS, PS, MS respectivly.

What i would like to do is have a macro to filter out one type of job and hide the rest, prefrably 3 seperate macros one for each type.

I currently have the spread sheet working by selecting individual columns with the correct data type in, copied below, but if we need to add a new project it wont capture it as it wont know to look, or a new column before the projects, as it wont line up anymore.


Sheets("Sheet2").Select
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Sheets("Sheet1").Select
Range("A2:E130").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("H:H,I:I,L:L,M:M,P:P").Select
Selection.Copy
Sheets("Sheet2").Select
Range("F1").Select
ActiveSheet.Paste

Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 4
Const LastRow As Long = 200

'< Set the columns that may contain data >
Const FirstCol As String = "D"
Const LastCol As String = "D"
'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = FirstRow To LastRow

'(we're using columns B to G here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

As you can see not very pretty.

So what i need is a macro similar to the hide rows = to 0 macro I found on this fabulous site. but it needs to hide columns that do not contain CS*

So firstly is it possible, i assume it is and probably quite easily.

I am leaving the company in 2 weeks and i really dont want to leave them with a tool that dosnt work properly.

I hope this makes sence, if not ill try and help.

lucas
03-14-2008, 08:40 AM
so your parts are in column A and your cusomer abbreviation is (CL, etc.)is in column B?

You wish to be able to filter by individual customers?

chrishill123
03-14-2008, 08:46 AM
Parts are in column A, job numbers are in row 1 starting on column I.

I did try and past a section of sheet but it went a little wrong.

lucas
03-14-2008, 08:56 AM
I'm still confused.....maybe take a look at this example and tell us where I am not understanding. This one filters for CL in column B

Sub CL()
With Worksheets("sheet1")
'The 2 in the next line tells it to look in column B
.Cells.AutoFilter Field:=2, Criteria1:="CL"
End With
End Sub

chrishill123
03-14-2008, 09:02 AM
I'm still confused.....maybe take a look at this example and tell us where I am not understanding. This one filters for CL in column B

Sub CL()
With Worksheets("sheet1")
'The 2 in the next line tells it to look in column B
.Cells.AutoFilter Field:=2, Criteria1:="CL"
End With
End Sub

Ok I have uploaded the sheet I am working on, I didnt know I could.

As you can in it I have part numbers in a, scheme numbers in I1 to W1, below each scheme number is how many of each part is required.

chrishill123
03-17-2008, 06:32 AM
So can anyone help?

chrishill123
03-20-2008, 03:57 AM
Sorry to bump this again, but after the easter weekend i only have 4 days left where I am and I really want to leave this complete for them.

All im really needing is for the macro to filter the columns, as the rows are done, in situ and not paste to another page as it currently does.

If it is not possible ill just have to explain how to add new columns to the macro when tey get new schemes.

Bob Phillips
03-20-2008, 04:32 AM
.

chrishill123
03-20-2008, 04:38 AM
Hi xld, thanks for the help, but it isnt what im looking for, if you can have a look at the file i attached in post 5 you can see what im trying to achive, as i have it working until a new scheme needs adding.

Its probably best to download it and rather than running it through IE as it dosnt format correctly.

Cheers
Chris

Bob Phillips
03-20-2008, 04:43 AM
I downloaded Lucas's effort. I will look again.

chrishill123
03-20-2008, 04:49 AM
Im sorry if I have offended but your file dosnt seem to do what I need it to, it seems to be more like the one Lucas provided.

If I have missed some thing and there is a way to implement that on to the CAP2 Planner file to replace my current macros please explain how as I cant see how you have created your filter.

Bob Phillips
03-20-2008, 05:00 AM
Why not do it all on sheet1 and hide the columns you don't want?

Bob Phillips
03-20-2008, 05:01 AM
Im sorry if I have offended but your file dosnt seem to do what I need it to, it seems to be more like the one Lucas provided.

If I have missed some thing and there is a way to implement that on to the CAP2 Planner file to replace my current macros please explain how as I cant see how you have created your filter.

Apologies are mine, I downloaded the wrong file. I corrected my post but you obviously read it before I did.

chrishill123
03-20-2008, 05:06 AM
Why not do it all on sheet1 and hide the columns you don't want?

Really I do want it on one sheet but the only way i could get a macro to do what i wanted was to copy the comluns i wanted to another sheet.

The file will eventually be used by several departments who need different parts of the info, so I wanted to be able to give it to them they just hit the button and it filters out the extra info not needed.

Ideally what I would like is a macro like the one used to filter out rows with nothing in them, only for columns, I have spent 6 or 7 hours trying to rework that macro to no avail, and im just going mad at the minute.

Bob Phillips
03-20-2008, 05:12 AM
I am busy for the next hour or so, I will retrun to it then.

chrishill123
03-20-2008, 05:14 AM
Really appriciate any help you can give, and no need to rush to help if your busy. Ive got till next Friday

Bob Phillips
03-20-2008, 06:46 AM
Try this

chrishill123
03-20-2008, 06:57 AM
That is perfect.
Thanks alot for this, for future refferance roughly how did you do it?

Its a much more elegant solution than I had as well.

Bob Phillips
03-20-2008, 07:10 AM
First I determinethe last column in row 1.

I then loaded a collection with all of the unique values (first two letters), and added the collection to the combobox.

When the combobox is clicked I save the value in B1 (for the SUMPRODUCT formula), and then I go and hide all columns from I on where row 1 doesn't start with the selection.

I then hide all rows where the formula now shows zero (using the SP formula), similar to what you were doing, but I simplified the code.