Create macros to automate Excel work

[jasonkgreen]

I have a data set of just under 60,000 records. It starts with 103 columns. I have recorded a macro to delete 77 of these columns.

I then delete around 50k of these records based on "status_type" (not active). I'm not sure my macro for this is the most efficient. My first question: what's the best way to highlight all the visible rows so they can be deleted, cut, or copied? Below is what I have right now. My concern with the VBA code below is that if my filtered rows are 1109 thru 4574, will the code below delete only those visible rows?

'1. Apply Filter
ws.Range("A3:Z60000").AutoFilter Field:=15, Criteria1:="4"

'2. Delete Rows
Application.DisplayAlerts = False
ws.Range("A3:Z60000").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True

 

[Fluff]

Will the rows to be deleted be in one group, or can they be scattered throughout the data?

 

[jasonkgreen]

Will the rows to be deleted be in one group, or can they be scattered throughout the data?

So that raises questions, do I even need to filter the data or can I delete rows based on criteria in one or multiple columns? Should I sort the data before filtering and then delete as a group. I know how to do all of this manually, my challenge is running this weekly and automating as much as possible.

 

[Fluff]

Could you answer my question?

 

[jasonkgreen]

Could you answer my question?

I'm a little unclear on your question. I suppose it could be done either way.

 

[Fluff]

Is col Z the last column of data?

 

[jasonkgreen]

Is col Z the last column of data?

After the unnecessary columns are deleted, yes, column Z is the last column.

 

[Fluff]

Ok Try this on a COPY of your sheet.

Sub jasonkgreen()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, i As Long
   
   Ary = Range("O3", Range("O" & Rows.Count).End(xlUp)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   For r = 1 To UBound(Ary)
      If Ary(r, 1) = 4 Then
         Nary(r, 1) = 1
         i = i + 1
      End If
   Next r
   If i > 0 Then
   Application.ScreenUpdating = False
   With Range("A3").Resize(UBound(Ary), 27)
      .Columns(27).Value = Ary
      .Sort .Columns(27), xlAscending, , , , , , xlNo
      .Resize(i).EntireRow.Delete
   End With
   Application.ScreenUpdating = True
   End If
End Sub

Code is pinched from Peter_SSs

 

[jasonkgreen]

Ok Try this on a COPY of your sheet.

Sub jasonkgreen()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, i As Long
  
   Ary = Range("O3", Range("O" & Rows.Count).End(xlUp)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   For r = 1 To UBound(Ary)
      If Ary(r, 1) = 4 Then
         Nary(r, 1) = 1
         i = i + 1
      End If
   Next r
   If i > 0 Then
   Application.ScreenUpdating = False
   With Range("A3").Resize(UBound(Ary), 27)
      .Columns(27).Value = Ary
      .Sort .Columns(27), xlAscending, , , , , , xlNo
      .Resize(i).EntireRow.Delete
   End With
   Application.ScreenUpdating = True
   End If
End Sub

Code is pinched from Peter_SSs

I copied the code exactly as it is above and pasted it into the VBA editor. It just gets caught in a loop between the If Ary(r, 1) line and the Next r line.

 

[Fluff]

What do you mean it "gets caught in a loop"?