Using Macro to hide rows that have a "0" quantity value
 

Hi all and thanks for any help at all.

Basically Im creating a full list of all the parts we carry in our company.

Every single part(row) will start off with a quantity of 0 and have the
description next to it etc.

If I put a quantity greater than 0 in the row for that part, I want to be
able to push cntrl+C to hide all the rows(parts) that are 0 quantity and only
show the rows that have quantity greater than 0.

Then if I want to see everything again that has the 0 quantity, to push
cntrl+B.

Thanks for any help!

Sincerely


Neil

Using Macro to hide rows that have a "0" quantity value
 

Hey Neil place this code into the VBE (visual basic editor)

In the sheet level module (right click on the sheet tab and click view
code) copy and paste this code:

Private Sub Worksheet_Activate()
On Error Resume Next
Application.MacroOptions Macro:="MyHide", _
HasShortcutKey:=True, ShortcutKey:="c"
Application.MacroOptions Macro:="Myshow", _
HasShortcutKey:=True, ShortcutKey:="b"
End Sub

Then click on Insert == Module and paste this code into the window:

Sub MyHide()
Dim mcell As Range
For i = Range("A1", Cells(Rows.Count, "A"). _
End(xlUp)).Count To 1 Step -1
With Cells(i, 1)
If .Value = 0 Then
.Rows.Hidden = True
End If
End With
Next
End Sub

Sub MyShow()
ActiveSheet.Rows.Hidden = False
End Sub


Hope this is what you were looking for...

Sandy
Neil wrote:
Hi all and thanks for any help at all.

Basically Im creating a full list of all the parts we carry in our company.

Every single part(row) will start off with a quantity of 0 and have the
description next to it etc.

If I put a quantity greater than 0 in the row for that part, I want to be
able to push cntrl+C to hide all the rows(parts) that are 0 quantity and only
show the rows that have quantity greater than 0.

Then if I want to see everything again that has the 0 quantity, to push
cntrl+B.

Thanks for any help!

Sincerely


Neil

Using Macro to hide rows that have a "0" quantity value
 

Thanks Sandy!
I copied and pasted to the correct locations but when I push cntrl+b or
cntrl+c
it just does the copy function or bold function.

"Sandy" wrote:

Hey Neil place this code into the VBE (visual basic editor)

In the sheet level module (right click on the sheet tab and click view
code) copy and paste this code:

Private Sub Worksheet_Activate()
On Error Resume Next
Application.MacroOptions Macro:="MyHide", _
HasShortcutKey:=True, ShortcutKey:="c"
Application.MacroOptions Macro:="Myshow", _
HasShortcutKey:=True, ShortcutKey:="b"
End Sub

Then click on Insert == Module and paste this code into the window:

Sub MyHide()
Dim mcell As Range
For i = Range("A1", Cells(Rows.Count, "A"). _
End(xlUp)).Count To 1 Step -1
With Cells(i, 1)
If .Value = 0 Then
.Rows.Hidden = True
End If
End With
Next
End Sub

Sub MyShow()
ActiveSheet.Rows.Hidden = False
End Sub


Hope this is what you were looking for...

Sandy
Neil wrote:
Hi all and thanks for any help at all.

Basically Im creating a full list of all the parts we carry in our company.

Every single part(row) will start off with a quantity of 0 and have the
description next to it etc.

If I put a quantity greater than 0 in the row for that part, I want to be
able to push cntrl+C to hide all the rows(parts) that are 0 quantity and only
show the rows that have quantity greater than 0.

Then if I want to see everything again that has the 0 quantity, to push
cntrl+B.

Thanks for any help!

Sincerely


Neil

Using Macro to hide rows that have a "0" quantity value
 

Try pasting this into the ThisWorkbook level in VBE it will run when
the workbook is opened.

Open VBE (Alt + F11), open Project Explorer (Ctrl + r), double click on
ThisWorkbook and paste the code below. After pasting save workbook,
close workbook, then reopen and try using the shortcut keys.

Private Sub Workbook_Open()
On Error Resume Next
Application.MacroOptions Macro:="MyHide", _
HasShortcutKey:=True, ShortcutKey:="c"
Application.MacroOptions Macro:="Myshow", _
HasShortcutKey:=True, ShortcutKey:="b"
End Sub

HTH
Sandy

Neil wrote:
Thanks Sandy!
I copied and pasted to the correct locations but when I push cntrl+b or
cntrl+c
it just does the copy function or bold function.

"Sandy" wrote:

Hey Neil place this code into the VBE (visual basic editor)

In the sheet level module (right click on the sheet tab and click view
code) copy and paste this code:

Private Sub Worksheet_Activate()
On Error Resume Next
Application.MacroOptions Macro:="MyHide", _
HasShortcutKey:=True, ShortcutKey:="c"
Application.MacroOptions Macro:="Myshow", _
HasShortcutKey:=True, ShortcutKey:="b"
End Sub

Then click on Insert == Module and paste this code into the window:

Sub MyHide()
Dim mcell As Range
For i = Range("A1", Cells(Rows.Count, "A"). _
End(xlUp)).Count To 1 Step -1
With Cells(i, 1)
If .Value = 0 Then
.Rows.Hidden = True
End If
End With
Next
End Sub

Sub MyShow()
ActiveSheet.Rows.Hidden = False
End Sub


Hope this is what you were looking for...

Sandy
Neil wrote:
Hi all and thanks for any help at all.

Basically Im creating a full list of all the parts we carry in our company.

Every single part(row) will start off with a quantity of 0 and have the
description next to it etc.

If I put a quantity greater than 0 in the row for that part, I want to be
able to push cntrl+C to hide all the rows(parts) that are 0 quantity and only
show the rows that have quantity greater than 0.

Then if I want to see everything again that has the 0 quantity, to push
cntrl+B.

Thanks for any help!

Sincerely


Neil

Using Macro to hide rows that have a "0" quantity value
 

I got it to work thanks!

Sandy is there anyway to make the hide process faster? With approx. 6000
rows of items, it takes about 1-3 minutes for it to hide everything. Any
suggestions?

"Sandy" wrote:

Try pasting this into the ThisWorkbook level in VBE it will run when
the workbook is opened.

Open VBE (Alt + F11), open Project Explorer (Ctrl + r), double click on
ThisWorkbook and paste the code below. After pasting save workbook,
close workbook, then reopen and try using the shortcut keys.

Private Sub Workbook_Open()
On Error Resume Next
Application.MacroOptions Macro:="MyHide", _
HasShortcutKey:=True, ShortcutKey:="c"
Application.MacroOptions Macro:="Myshow", _
HasShortcutKey:=True, ShortcutKey:="b"
End Sub

HTH
Sandy

Neil wrote:
Thanks Sandy!
I copied and pasted to the correct locations but when I push cntrl+b or
cntrl+c
it just does the copy function or bold function.

"Sandy" wrote:

Hey Neil place this code into the VBE (visual basic editor)

In the sheet level module (right click on the sheet tab and click view
code) copy and paste this code:

Private Sub Worksheet_Activate()
On Error Resume Next
Application.MacroOptions Macro:="MyHide", _
HasShortcutKey:=True, ShortcutKey:="c"
Application.MacroOptions Macro:="Myshow", _
HasShortcutKey:=True, ShortcutKey:="b"
End Sub

Then click on Insert == Module and paste this code into the window:

Sub MyHide()
Dim mcell As Range
For i = Range("A1", Cells(Rows.Count, "A"). _
End(xlUp)).Count To 1 Step -1
With Cells(i, 1)
If .Value = 0 Then
.Rows.Hidden = True
End If
End With
Next
End Sub

Sub MyShow()
ActiveSheet.Rows.Hidden = False
End Sub


Hope this is what you were looking for...

Sandy
Neil wrote:
Hi all and thanks for any help at all.

Basically Im creating a full list of all the parts we carry in our company.

Every single part(row) will start off with a quantity of 0 and have the
description next to it etc.

If I put a quantity greater than 0 in the row for that part, I want to be
able to push cntrl+C to hide all the rows(parts) that are 0 quantity and only
show the rows that have quantity greater than 0.

Then if I want to see everything again that has the 0 quantity, to push
cntrl+B.

Thanks for any help!

Sincerely


Neil