CODEWITHSUNDEEP
vbaexcel
user3466159
user3466159

Reputation: 27

Cleaning up repetitive VBA code

I have some code that works but I think could be written much better. I'm not very savvy with this, but is there a way I could clean this up? It goes through and hides rows of more data on a separate sheet if cell A12 is < 40.

Option Explicit
Sub Main()
Dim wsa As Worksheet: Set wsa = ActiveWorkbook.Sheets("Sheet A")
Dim wse As Worksheet: Set wse = ActiveWorkbook.Sheets("Sheet E")
Dim wsf As Worksheet: Set wsf = ActiveWorkbook.Sheets("Sheet F")
Dim wsg As Worksheet: Set wsg = ActiveWorkbook.Sheets("Sheet G")
Dim wsk As Worksheet: Set wsk = ActiveWorkbook.Sheets("Sheet K")

   If wsa.Range("A12").Value < "40" Then
  Range("A_Data").EntireRow.Hidden = True
     Else
  Range("A_Data").EntireRow.Hidden = False
   End If

   If wse.Range("A12").Value < "40" Then
  Range("E_Data").EntireRow.Hidden = True
  Else
  Range("E_Data").EntireRow.Hidden = False
   End If

   If wsf.Range("A12").Value < "40" Then
  Range("F_Data").EntireRow.Hidden = True
  Else
  Range("F_Data").EntireRow.Hidden = False
   End If

   If wsg.Range("A12").Value < "40" Then
  Range("G_Data").EntireRow.Hidden = True
  Else
  Range("G_Data").EntireRow.Hidden = False
   End If

   If wsf.Range("A12").Value < "40" Then
  Range("K_Data").EntireRow.Hidden = True
  Else
  Range("K_Data").EntireRow.Hidden = False
   End If

Exit Sub

Any help would be greatly appreciated, I'm having a hard time figuring this out. Thanks for your time.

Upvotes: 1

Views: 106

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

I would do this one:

Sub Main()
    Dim x
    For Each x In Array("A", "E", "F", "G", "K")
        Range(x & "_Data").EntireRow.Hidden = _
            (CDbl(ActiveWorkbook.Worksheets("Sheet " & x).Range("A12").Value) < 40)
    Next x
End Sub

I also use

CDbl(ActiveWorkbook.Worksheets("Sheet " & x).Range("A12").Value) < 40

because string comparison may give you unexpected result (say "2">"10" returns TRUE).

Upvotes: 1

Related Questions