VBA/Formula needed to automatically delete columns and rows

[XLWiz]

Hello all,

I have a monthly spreadsheet I pull that contains raw data in columns A through AU. I need a VBA or formula that will enable me to instantly delete specific columns I don't need. If possible, I also need for the formula/VBA to delete rows based on column E's values.

Thanks in advance.

 

[HalfAce]

Hi,
1. We can't use a formula to delete rows, columns or anything else, so you're looking at using vba for this.
2. Which columns are you deleting? (Always the same columns, or are they determined some other way? (If so, how are they determined?)
3. What value(s) in column E are you looking for to decide which rows to delete?

Oh, and when you say you want to delete rows based on column E's values, do you mean column E BEFORE other columns are deleted, or column E AFTER some columns are deleted? (Assuming any columns between A & E are being deleted.)

 

[XLWiz]

Hello HalfAce,

1. VBA will work.

2. I am always deleting columns A,B, F, G, H, I, J, P, Q, R, S, W, X, Y, Z, AA, and AD-AU

3. Column E contains the following coverage statuses: Covered, No Election, Not Covered

Column E typically becomes column C after I work the data and delete unneeded columns. The rows I delete are for all statuses that are Covered and Not Covered, leaving only No Election available. This deletion can be done either before or after the columns I previously described are deleted (whichever is easiest).

Hope that cleared things up a bit more. I appreciate your assistance.

 

[HalfAce]

Here's something you might try.
It assumes you do have a header in row 1 - for filtering/row deleting purposes in column E.

Sub RetainNoElectionRows()
Dim LstRwE As Long
LstRwE = Cells(Rows.Count, "E").End(xlUp).Row
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.Range("E1:E" & LstRwE).AutoFilter Field:=1, Criteria1:="<>No Election"
Range("E2:E" & LstRwE).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0
ActiveSheet.AutoFilterMode = False
Columns("AD:AU").EntireColumn.Delete
Columns("W:AA").EntireColumn.Delete
Columns("P:S").EntireColumn.Delete
Columns("F:J").EntireColumn.Delete
Columns("A:B").EntireColumn.Delete
Application.ScreenUpdating = True
End Sub

That come pretty close to what you're looking for?

 

[XLWiz]

HalfAce,

This worked PERFECTLY! YOU ROCK!!

Thank you!!!

 

[XLWiz]

Here's something you might try.
It assumes you do have a header in row 1 - for filtering/row deleting purposes in column E.

Sub RetainNoElectionRows()
Dim LstRwE As Long
LstRwE = Cells(Rows.Count, "E").End(xlUp).Row
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.Range("E1:E" & LstRwE).AutoFilter Field:=1, Criteria1:="<>No Election"
Range("E2:E" & LstRwE).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0
ActiveSheet.AutoFilterMode = False
Columns("AD:AU").EntireColumn.Delete
Columns("W:AA").EntireColumn.Delete
Columns("P:S").EntireColumn.Delete
Columns("F:J").EntireColumn.Delete
Columns("A:B").EntireColumn.Delete
Application.ScreenUpdating = True
End Sub

That come pretty close to what you're looking for?

Hello HalfAce,

If I wanted to modify your code to sort column E oldest to newest rather than delete data, how would I go about making this adjustment?

Thanks again for your time.

 

[HalfAce]

I don't understand.
How can we sort older to newer on the values "Covered", "Not Covered" and "No Election"?

 

[XLWiz]

I don't understand.
How can we sort older to newer on the values "Covered", "Not Covered" and "No Election"?

My apologies for the confusion. What I'm actually doing is applying your code to a different report I recently began pulling. The new report has a date range in column AV, that I sort from oldest to newest, before deleting several columns I don't need.

Does this provide clarification?