Macro to delete rows as per the column header

[TopLearner]

Good afternoon everybody,



I hope you are well.


I have a sheet (“Sheet1”) with data on it.

I want to write a code that does the following actions:

• Delete the first row
• Delete the columns that have the following headers: "Employee Number", "Pay Class Name", "Pay Type Name", "Job Name", "Pay Category Name", "Employee Punch Employee Comment", "Employee Punch Manager Comment", "Employee Pay Adjust Employee Comment", "Employee Pay Adjust Manager Comment"
• Find and select the column whose header is “Site”
• Delete those rows where in column “Site” cells say (Australia)

I know how to do the 1 and 2 steps but I am stuck with the 3rd and 4th.

Can you kindly help?


My code is

Sub cleanupsheet()


Dim ws As Worksheet

Dim i As Long


Rows(1).EntireRow.Delete


Set ws = Sheet1


With ws


For i = 16 To 1 Step -1


Select Case .Cells(1, i).Value


Case "Employee Number", "Pay Class Name", "Pay Type Name", "Job Name", "Pay Category Name", "Employee Punch Employee Comment", "Employee Punch Manager Comment", "Employee Pay Adjust Employee Comment", "Employee Pay Adjust Manager Comment"


.Columns(i).Delete


End Select

Next i


End With


Dim Location As Range

Set Location = Rows(1).Find("Site")

Thanks

Sanchez

 

[HaHoBe]

Hi TopLearner,

maybe using AutoFilter (change for Criteria to suit)

Public Sub MrE_1223031_1614811()
' https://www.mrexcel.com/board/threads/macro-to-delete-rows-as-per-the-column-header.1223031/
' Created: 20221124
' By:      HaHoBe
Dim i       As Long
Dim var     As Variant
Dim ws      As Worksheet

Set ws = Sheet1       'codename for sheet

With ws
  .Rows(1).Delete
  For i = .Cells(1, .Columns.Count).End(xlToLeft).Column To 1 Step -1
    Select Case .Cells(1, i).Value
      Case "Employee Number", "Pay Class Name", "Pay Type Name", "Job Name", "Pay Category Name", "Employee Punch Employee Comment", _
          "Employee Punch Manager Comment", "Employee Pay Adjust Employee Comment", "Employee Pay Adjust Manager Comment"
        .Columns(i).Delete
    End Select
  Next i
  var = Application.Match("Site", .Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft)), 0)
  If IsNumeric(var) Then
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("A1").CurrentRegion.AutoFilter field:=var, Criteria1:="(Australia)"
    With .Range("A2", .Cells(.Rows.Count, var).End(xlUp)).SpecialCells(xlCellTypeVisible)
      .Delete xlShiftUp
    End With
    .AutoFilterMode = False
  End If
End With

Set ws = Nothing

End Sub

Ciao,
Holger

 

[TopLearner]

Hi TopLearner,

maybe using AutoFilter (change for Criteria to suit)

Public Sub MrE_1223031_1614811()
' https://www.mrexcel.com/board/threads/macro-to-delete-rows-as-per-the-column-header.1223031/
' Created: 20221124
' By:      HaHoBe
Dim i       As Long
Dim var     As Variant
Dim ws      As Worksheet

Set ws = Sheet1       'codename for sheet

With ws
  .Rows(1).Delete
  For i = .Cells(1, .Columns.Count).End(xlToLeft).Column To 1 Step -1
    Select Case .Cells(1, i).Value
      Case "Employee Number", "Pay Class Name", "Pay Type Name", "Job Name", "Pay Category Name", "Employee Punch Employee Comment", _
          "Employee Punch Manager Comment", "Employee Pay Adjust Employee Comment", "Employee Pay Adjust Manager Comment"
        .Columns(i).Delete
    End Select
  Next i
  var = Application.Match("Site", .Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft)), 0)
  If IsNumeric(var) Then
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("A1").CurrentRegion.AutoFilter field:=var, Criteria1:="(Australia)"
    With .Range("A2", .Cells(.Rows.Count, var).End(xlUp)).SpecialCells(xlCellTypeVisible)
      .Delete xlShiftUp
    End With
    .AutoFilterMode = False
  End If
End With

Set ws = Nothing

End Sub

Ciao,
Holger

Thank you Holger,

I have a question. After deleting all the columns that I don't need
"Employee Number", "Pay Class Name", "Pay Type Name", "Job Name", "Pay Category Name", "Employee Punch Employee Comment", _
"Employee Punch Manager Comment", "Employee Pay Adjust Employee Comment", "Employee Pay Adjust Manager Comment", the "site" column which is the one that contains the word "Australia", becomes the third column.

Therefore, I could use this code

Sub test()

With [A1].CurrentRegion
   .AutoFilter 3, "*Australia*"
   .Offset(1).EntireRow.Delete
   .AutoFilter
End With

End Sub

However, on other files I have the column order may vary so it is not the third column.
Is it the reason why you use these lines?

 var = Application.Match("Site", .Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft)), 0)
  If IsNumeric(var) Then
    If .AutoFilterMode Then .AutoFilterMode = False

Could you kindly explain them to me? Why cells(1,"A")?

Thanks
Sanchez

 

[HaHoBe]

Hi Sanchez,

Is it the reason why you use these lines?

Definitely. The codeline in question will try to find the requested item in the first row and will

• find the item and deliver the column number where it was found (from the column you started with, in my case I choose this to be Column A or 1)
• return an error if the item is not found

The next codelines checks if the variable returns a number (meaning that the item is found and will apply the AutoFilter in the column of match) or skip that section.

.Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft))

This is building the range to search in. As I find it easier to do so using Cells (starting with the row number as first parameter and allowing both characters for Columns as well as numbers) the first cells is equal to the Address of the first row in Column A (meaning A1 or R1C1). The second cell gives a range object while starting at the far right end of the columns in the given row and going left to meet the first cell with an entry in the row (in this case start with XFD1 and referring to the range of the last filled cell in row 1 (if there are any blanks in the headers they will be ignored by this method).

HTH,
Holger

 

[TopLearner]

Hi

Hi Sanchez,



Definitely. The codeline in question will try to find the requested item in the first row and will

• find the item and deliver the column number where it was found (from the column you started with, in my case I choose this to be Column A or 1)
• return an error if the item is not found

The next codelines checks if the variable returns a number (meaning that the item is found and will apply the AutoFilter in the column of match) or skip that section.

.Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft))

This is building the range to search in. As I find it easier to do so using Cells (starting with the row number as first parameter and allowing both characters for Columns as well as numbers) the first cells is equal to the Address of the first row in Column A (meaning A1 or R1C1). The second cell gives a range object while starting at the far right end of the columns in the given row and going left to meet the first cell with an entry in the row (in this case start with XFD1 and referring to the range of the last filled cell in row 1 (if there are any blanks in the headers they will be ignored by this method).

HTH,
Holger

Hi Holger,

Thanks again.

However, I am still struggling to understand it.
so, after removing all the columns that I do not need
"Employee Number", "Pay Class Name", "Pay Type Name", "Job Name", "Pay Category Name", "Employee Punch Employee Comment", _
"Employee Punch Manager Comment", "Employee Pay Adjust Employee Comment", "Employee Pay Adjust Manager Comment"

My column site is going to be the third column, column C
Then, with the following code:

var = Application.Match("Site", .Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft)), 0)

I am saying, with the match function, return the position of "Site" which is the header of the column I am looking for and it is in row 1:

As per the match function arguments:
"Site" is the lookup value

lookup array: would be .Range(.Cells(1, "A") which means row 1, column A. Why column A if "Site" is in column C?

match type: .Cells(1, Columns.Count).End(xlToLeft)), 0) I do not understand this part


Sorry and thanks again
Sanchez

 

[HaHoBe]

Hi Sanchez,

the lookup array is the whole expression building the range

.Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft))

The Match Part is 0 in the code regarding these arguments

• 1 = Less Than
• 0 = Exact Match
• -1 = Greater Than

Ciao,
Holger

 

[TopLearner]

Hi Sanchez,

the lookup array is the whole expression building the range

.Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft))

The Match Part is 0 in the code regarding these arguments

• 1 = Less Than
• 0 = Exact Match
• -1 = Greater Than

Ciao,
Holger

Thanks Holger,

this is the part that I dont understand so I will break it down:

.Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft))

Is it telling Excel to look for "Site":

Range(.Cells(1, "A")

: in row 1, column A which is the cell A1

.Cells(1, Columns.Count).End(xlToLeft))

: I don´t understand this part. So is it saying go from the last column of the sheet (XFD1) to the first one with data (in my case is column G), basically G1?


Then, if you join both

.Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft))

Does it mean go from A1 to G1?


Thanks
Sanchez

 

[HaHoBe]

Hi Sanchez,

I will try and make it more obviously to you

      .Range(.Cells(1, "A"), .Cells(1, Columns.Count).End(xlToLeft))

A range kann be set by using a string like "A1:G1" or like above by giving 2 cells values. The first we look at

.Cells(1, "A")

is static referencing the start cell which is equal to A1.

.Cells(1, Columns.Count).End(xlToLeft)

is dynamic and tells Excel to start at the top most right cell in Row 1 and go to the left until an entry in a cell in Row 1 is found and take that cell as the end for the range to work on (we must not know the number of columns but this command will set the range for the last cell depending on entries).

There are lots of different ways to get the information needed, sorry for not being to clear about this.

Ciao,
Holger