CODEWITHSUNDEEP
vbaexcel
user3258186
user3258186

Reputation: 11

Hide Rows Based on Date in VBA

I have a multiple worksheets within a workbook that have every day spelled out in column A (A2:A367). Upon opening the workbook, I am trying to hide all rows not pertaining to the current month. I'm not sure if there is a way to automatically pull the current date from Excel and have the rows adjusted. I am currently writing code to activate every worksheet within the workbook and go thru each row and hide rows from other months. Is there a more efficient way to loop and hide rows based on month?

"Regionwide" is first worksheet of 8.

Sub Macro3()

Sheets("Regionwide").Select
Range("A2").Select

Dim cell As Range
Const Month As String = "2"
'Dim Month As String
'Month = MonthName(2)
 Dim LR As Long, I As Long
 LR = Range("A" & Rows.Count).End(xlUp).Row
 For I = 2 To LR
 If Left(Range("A" & I).Value, 2) <> Month Then
 cell.EntireRow.Hidden = True
   ' Range("AC" & I).Value = "Reinsurance"
 'Else
  '  cell.EntireRow.Hidden = True

  End If
  Next I

 End Sub

Upvotes: 0

Views: 1247

Answers (1)

Bwurk
Bwurk

Reputation: 131

You can get today's month with:

Dim intMonth As Integer
intMonth = Month(Now())

You can also loop through all the sheets with something like:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
  LR = ws.Range("A" & Rows.Count).End(xlUp).Row
  ...
Next

You don't have to select sheets or cells to perform actions on it. Refering to it through ActiveWorkbook.Sheets(index).Range(range) can speed up your code.

Upvotes: 0

Related Questions