Deleting Rows to have a specific year

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
846
Hello all,
I have a large data set with years of 2019, 2020, 2021, in column A.
I want to filet and delete all rows that do not have a year of 2021, so I only have 2021 showing...all other rows are deleted.

I have the following code:
VBA Code:
With Sheets("HV-1")
    Sheets("HV-1").Select
    Cells.Select
    Selection.AutoFilter
      With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        .AutoFilter 1, "2019*"
        .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
        .AutoFilter 1, "2020*"
        .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      End With
    .AutoFilterMode = False
End With

This does work, however, if I do not have a year of 2019, it will fault on me. Is there a better way to filter and delete all rows other than 2021, show only 2021 is left showing?
Thanks for the help
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
With Sheets("HV-1")
   .Range("A2", .Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "<>2021"
   .AutoFilter.Range.Offset(1).EntireRow.Delete
   .AutoFilterMode = False
End With
 

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
846
Hello,
I tried the above code, and it deleted all rows, including rows with 2021
Thanks for the help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
What exactly is in col A?
 

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
846

ADVERTISEMENT

A CSV Text, that starts at the very beginning as: 2020-3-24:6_12_29 (all the way to around row 16K)
I want to start the entire program with fileting out all the unnecessary dates (<>2021), simply to have a smaller data set to work with.
From there, I go into "Text to Columns" to clean it up and brake it up into Date for Column A, and Time in Column B. I use the recorder for the Text to Column:

VBA Code:
'Text to Columns
'Split Date/Time
Columns("A:A").Select
Selection.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Space:=True
Selection.NumberFormat = "m/d/yyyy;@"
Columns("B:B").Select
Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"

Thanks for the help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
In that case try
VBA Code:
.Range("A2", .Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "<>2021*"
 

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
846
In that case try
VBA Code:
.Range("A2", .Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "<>2021*"
So I'm using your suggestion styled in the code below, which is working.
VBA Code:
With Sheets("Spray Booth 1")
    Sheets("Spray Booth 1").Select
    Cells.Select
    Selection.AutoFilter
      With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
        .AutoFilter 1, "<>2021*"
        .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      End With
    .AutoFilterMode = False
End With

If there is a cleaner way to use this, I would be interested!
Thanks for the help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
Yes, the way I showed. If you do not have any cells that start 2021 you will get an error.
 

Forum statistics

Threads
1,147,962
Messages
5,744,053
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top