is there a way to delete rows based on a condition in a macro?
for example: I want to delete all rows that doesn't contain "x" in Column E.
or: I want to delete all columns that contain "x"
Is there a simple way to write this for either example?
Hi Julie,
Yes this can be done. But the final macro will depend on what your data looks like. Can you please supply a workbook with data?
Regards
Phil
There are actually 2 different reports that look very similar.
I only supplied one. In one I need to delete rows in which column c does not contain "x".
In both I need to delete columns that contain "pto" "web cam" "employee id" etc. (it's different for each workbook, but the action is the same)
So I would like to know how to write each action correctly.
Hi Julie,
Are we deleting rows or columns?
Which column(s) would pto, web cam, employee id etc appear?
In order to look for something specific to delete, the code needs to know exactly what it is looking for. If it's different for each workbook, then the macro may need to be different for each workbook.
My first thoughts are that if you use tables we can make use of structured references in our code.
Regards
Phil
in one workbook I delete just columns they are titled "web cam" and "ip address" in one workbook. and once in a while "pto" if it has that column that day.
In the other workbook i delete columns titled "employee id" "pto" "paid time off" "status" etc, same as above it doesn't always have "pto"
I also delete all rows that don't contain "x" in column c. in this workbook.
We just format and print the report run. we don't actually work in the spreadsheet. So basically we want a macro to do the formatting for us and save us time.
I can record the macro to delete the columns but some days it contains "pto" and other days it doesn't, which would mean having 2 macros for each report.
So I'm trying to figure out how to write one but need simple instructions with some explanation as I'm new to it and trying to wrap my head around it.
When I record it this is what is says in the workbook where i only delete columns
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("J:K").Select
Selection.Delete Shift:=xlToLeft
But obviously that is going to always delete those columns so I feel like I need an IF command of some sort but dont' know how to write it.
Hi Julie
This question had already been answered before: