Reputation: 87
I am setting up a document to remove rows if a specific cell contains a value. I want this code to run when the 'save' button is selected. What do I need to add to my vba script to make this happen?
I have searched several sites and tried several suggested solutions but am not finding one that is working.
HideRows_BeforeSave()
Dim beginRow As Long
Dim endRow As Long
Dim chkCol As Long
Dim rowCnt As Long
Dim rngResult As Range
Dim ws As Worksheet
beginRow = 3
endRow = 38
chkCol = 14
Set ws = ThisWorkbook.Worksheets("Travel Expense Codes")
For rowCnt = beginRow To endRow
If Cells(rowCnt, chkCol).Value = "X" Then
Cells(rowCnt, chkCol).EntireRow.Hidden = True
Else
Cells(rowCnt, chkCol).EntireRow.Hidden = False
End If
Next rowCnt
When this code is inserted into the 'ThisWorkbook' object I am getting no response when I run the macro. When inserted into a 'Module', I can make the macro run but am not able to make it run with the 'save' selection.
Upvotes: 2
Views: 343
Reputation: 550
can you try this? as @braX suggested, you need to start from the last row and make your way up to the first row. This code lies in 'ThisWorkbook' see the red circles.
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim beginRow As Long, endRow As Long, chkCol As Long, rowCnt As Long
Dim rngResult As Range
Dim ws As Worksheet
beginRow = 3
endRow = 38
chkCol = 14
Set ws = ThisWorkbook.Worksheets("Travel Expense Codes")
For rowCnt = endRow To beginRow Step -1
If Cells(rowCnt, chkCol).Value = "X" Then
ws.Cells(rowCnt, chkCol).EntireRow.Delete
End If
Next rowCnt
End Sub
Upvotes: 1