How to run a macro if a column has a certain text in it
Category: [Excel] Demo Available
How to run a macro if a column has a certain text in it
Requirement:
The user wants a code to run only if any cell in column J has letter Y in it.
Solution:
Code:
Private Sub CommandButton2_Click()
If WorksheetFunction.CountIf(Range("J:J"), "Y") <> 0 Then
Dim I As Long
Set ws = Worksheets("DESPATCHED")
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Columns(10).AutoFilter 1, "Y"
With Range("a4", Range("I" & Rows.Count).End(3))
.Copy Sheet4.Cells(Rows.Count, 1).End(3).Offset(1)
.EntireRow.Delete
End With
Columns(10).AutoFilter
With Sheets("DESPATCHED")
I = .Range("A" & .Rows.Count).End(xlUp).Row
End With
ws.Range("G2:G" & I).Value = "Y"
Application.ScreenUpdating = True
End If
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to end a macro if cell blank or continue if populated |
| How to use a macro to copy data from multiple workbooks to one master sheet in another workbook |
| How to use a macro to run through sheet in excel and put double quotes around values |
| How to use a macro for grouping rows based on cells with same names |
| How to use a macro to pull every Nth row of data |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.