Forum

Notifications
Clear all

macro language

7 Posts
3 Users
0 Reactions
91 Views
(@dreamingbig2att-net)
Posts: 15
Eminent Member
Topic starter
 

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?

 
Posted : 22/12/2018 8:13 pm
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

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

 
Posted : 23/12/2018 11:39 pm
(@dreamingbig2att-net)
Posts: 15
Eminent Member
Topic starter
 

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.

 
Posted : 24/12/2018 11:51 am
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

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

 
Posted : 24/12/2018 7:01 pm
(@dreamingbig2att-net)
Posts: 15
Eminent Member
Topic starter
 

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.

 
Posted : 24/12/2018 7:19 pm
(@dreamingbig2att-net)
Posts: 15
Eminent Member
Topic starter
 

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.

 
Posted : 24/12/2018 7:47 pm
(@sunnykow)
Posts: 1417
Noble Member
Share: