Deleting rows in a list

Question

Okay so I may be a bit over my head, I started to mess with VBA Excel yesterday and I have almost completed my task. I have been trying to pull filtered information from a couple of different sheets through advanced filter and list it on one. But with the code that I have it brings the filtered information in and copies the header for each page that I pull information from. Therefore my list has some interruptions in it by these headers. The code that brings me to this point is as follows:

Sub Filter_Refresh()

' Filter_Refresh Macro

Sheets("55920000").Range("_5592[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Lists and Criteria").Range("D2:D3"), CopyToRange:= _
    Range("A1:AF1"), Unique:=False
y = Evaluate("=address(counta(a:a)+1,1,4)")
Sheets("55930000").Range("_5593[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Lists and Criteria").Range("D2:D3"), CopyToRange:= _
    Range(y), Unique:=False
x = Evaluate("=address(counta(a:a)+1,1,4)")
Sheets("55940000").Range("_5594[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Lists and Criteria").Range("D2:D3"), CopyToRange:= _
    Range(x), Unique:=False
Z = Evaluate("=address(counta(a:a)+1,1,4)")
Sheets("55950000").Range("_5595[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Lists and Criteria").Range("D2:D3"), CopyToRange:= _
    Range(Z), Unique:=False

End Sub

Basically I want to delete the rows with the headers and any suggestions to what i have already or how to set up variables more effectively would be amazing.

Mertinc · Answer 1 · 2017-05-18T17:10:02.427

0

The HeaderRowRange is an integral part of the ListObject as such it cannot be deleted. Therefore, if you need to delete the header data then you have to first convert the ListObject to a standard excel range using the Unlist method of the ListObject. Add this code into your procedure:

The code is deleting the entire header rows through entire worksheets..

Bonus Answer (Loops through all sheets and delete entire header rows.)

Option Explicit

Sub DeleteHeaderRows()

Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Dim lob As ListObject
Dim tblHdr As Range
    For Each ws In ThisWorkbook.Worksheets
    MsgBox ws.Name 'Shows you in which page code will run. you can comment out this.
        For Each lob In ws.ListObjects
            'These Fors loop through all of your lists in all of your sheets
            Set tblHdr = ws.ListObjects("" & lob & "").HeaderRowRange
            lob.Unlist 'Convert ListObject to a Range
            tblHdr.EntireRow.Delete  'Delete all row
        Next lob
    Next ws
End Sub

Main Answer (Loops within only one specific sheet and delete entire header rows except the first one)

Option Explicit
Sub DeleteHeaderRowsTekSayfada()
Dim wb As Workbook
Dim ws As Worksheet
Dim lob As ListObject
Dim tblHdr As Range, PassFrstHdr As Long, begin As Long
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Lists and Criteria")
    begin = 1
        For Each lob In ws.ListObjects
            If begin = 1 Then
            begin = begin + 1   'by the help of this equation we bypass the first header
            Else
            Set tblHdr = ws.ListObjects("" & lob & "").HeaderRowRange
            lob.Unlist 'Convert ListObject to a Range
            tblHdr.EntireRow.Delete  'Delete entire row
            End If
     Next lob
End Sub

edited May 18 '17 at 17:10

answered May 18 '17 at 16:14

Mertinc

793
2
13
27

Thank you for your response, some more information might be necessary. All the sheets have the same set up in the table and same header row. Therefore when I use advanced filter it brings all the information that I want into one place but I only need one header row a the top. When the information is compiled I want one constant list with one header but I get all the information I want with an extra header row for each sheet that was filtered through. This link explains something close to what I want but isn't working for me http://stackoverflow.com/questions/7851859/delete-a-row-in-excel-vba – Sam May 18 '17 at 16:33
in my code I have the variables x,y,z and they give me a references of A21,A71,A75 and therefore I want to delete rows 21, 71, and 75. – Sam May 18 '17 at 16:39
It's still not enough clear. So instead of looping through all worksheets, you are bringing other lists to 'Lists and Criteria' worksheet and in that sheet you want to leave only one header row and delete the rest. Is that correct what I understood? – Mertinc May 18 '17 at 16:41
@Sam please check the **Main Answer** in my edited answer. – Mertinc May 18 '17 at 17:11
you understand correctly. Except that the header row is no longer considered a header row but just another listed item. Therefore in my list of numbers I have some rows that consist of the words that are in the header, but can not be identified as a header row. Here is the link that I used to get to where I'm at although the person in this forum did not have an issue with removing the extra rows like I am. https://www.mrexcel.com/forum/excel-questions/75883-advanced-filter-multiple-sheets-back-one-how-can-i-do.html – Sam May 18 '17 at 20:38
Why you are telling each new detail of your question after I spend another hours? That's ridiculous.. Good luck on your research. – Mertinc May 19 '17 at 00:29
Thanks for the help I posted the solution to my issue above sorry for the poorly worded question. – Sam May 23 '17 at 14:34

score 0 · Accepted Answer · edited May 24 '17 at 10:12

Sub Filter_Refresh()
'
' Filter_Refresh Macro
'
Dim y


Sheets("55920000").Range("_5592[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Lists and Criteria").Range("D2:D3"), CopyToRange:= _
    Range("A2:AF2"), Unique:=False

y = Evaluate("=address(counta(a:a)+1,1,4)")

Sheets("55930000").Range("_5593[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Lists and Criteria").Range("D2:D3"), CopyToRange:= _
    Range(y), Unique:=False

Cells.Find(What:="Propoal #", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).EntireRow.Delete

x = Evaluate("=address(counta(a:a)+1,1,4)")

Sheets("55940000").Range("_5594[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Lists and Criteria").Range("D2:D3"), CopyToRange:= _
    Range(x), Unique:=False

Cells.Find(What:="Propoal #", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).EntireRow.Delete

Z = Evaluate("=address(counta(a:a)+1,1,4)")

Sheets("55950000").Range("_5595[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Lists and Criteria").Range("D2:D3"), CopyToRange:= _
    Range(Z), Unique:=False

Cells.Find(What:="Propoal #", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).EntireRow.Delete

End Sub

@Mertinc Hey thanks for putting the time into attempting to answer my poorly worded question here is the solution to my issue I referenced a word in my header row and deleted the row from there

Deleting rows in a list

2 Answers2

Bonus Answer (Loops through all sheets and delete entire header rows.)

Main Answer (Loops within only one specific sheet and delete entire header rows except the first one)