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.
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.