PDA

View Full Version : Solved: Can this code be shorten vba



VISHAL120
08-29-2011, 10:23 PM
Hi can you please help to shorten these codes:
sub show_Only_12
Application.ScreenUpdating = False
Sheets("KLoading").Select
Rows("3:32").Select

Selection.EntireRow.Hidden = False
With ActiveSheet
For Each cell In Range("X3:X32")
If cell.Value = "7" Then
cell.EntireRow.Hidden = True
End If
Next
End With
With ActiveSheet
For Each cell In Range("X3:X32")
If cell.Value = "12" Then
cell.EntireRow.Hidden = True
End If
Next
End With

With ActiveSheet
For Each cell In Range("X3:X32")
If cell.Value = "10" Then
cell.EntireRow.Hidden = True
End If
Next
End With

With ActiveSheet
For Each cell In Range("X3:X32")
If cell.Value = "0" Then
cell.EntireRow.Hidden = True
End If
Next
End With

Range("kLOADING!A1").Offset(Range("KL_Capacity_Area").Row - 1, Range("KL_Capacity_Area").Column - 1).Select
Selection.End(xlToRight).Select
Selection.End(xlToLeft).Select
Application.ScreenUpdating = True

many thanks for the help.

GTO
08-30-2011, 01:08 AM
Hi there,

I wasn't sure about the last part, but this may cover the looping:
Option Explicit

Sub show_Only_12()
Dim wksKLoading As Worksheet
Dim Cell As Range

Application.ScreenUpdating = False
Set wksKLoading = ThisWorkbook.Worksheets("KLoading")

With wksKLoading
.Rows("3:32").Hidden = False
For Each Cell In .Range("X3:X32")
Select Case Cell.Value
Case 0, 7, 10, 12
If Not Cell.Value = vbNullString Then Cell.EntireRow.Hidden = True
End Select
Next
End With
Application.ScreenUpdating = True
End Sub

Hope that helps,

Mark

Bob Phillips
08-30-2011, 01:25 AM
A slightly different approach



Sub show_Only_12()
Dim wksKLoading As Worksheet
Dim Cell As Range

Application.ScreenUpdating = False
Set wksKLoading = ActiveSheet 'ThisWorkbook.Worksheets("KLoading")

With wksKLoading
For Each Cell In .Range("X3:X32")
Cell.EntireRow.Hidden = Not Cell.Value = vbNullString And _
Not IsError(Application.Match(Cell.Value, Array(0, 7, 10, 12), 0))
Next
End With
Application.ScreenUpdating = True
End Sub

VISHAL120
08-30-2011, 03:24 AM
hi Bob, GTO,

thanks a lot for your help sir.

CatDaddy
08-30-2011, 01:23 PM
With ActiveSheet
For Each cell In Range("X3:X32")
If cell.Value = "0" or cell.Value = "7" or cell.Value = "10" or cell.Value = "12"Then
cell.EntireRow.Hidden = True
End If
Next
End With