Deleting below a certain variable row in Microsoft Excel

Question

I currently have code were I have a file of data with unique businesses, the vba that I have programmed removes all other businesses but one. I have noticed that the file that I have worked on has legacy data below the rows filled with data I need and I need to remove these to make the file smaller.

Sub ConstructionTools()
    Dim ARange As Range
    Dim DRange As Range
    Dim ws As Worksheet
    Dim wsB As Worksheet
    Dim filename As String

    Set ws = Sheets("Data")
    Set wsB = Sheets("Macro")
    Set DRange = Nothing

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

For Each ARange In ws.Range("L1:L28000").Rows
    If ARange(1).Value = "BUILDING CONSTRUCTION" Or ARange(1).Value = "CONSTRUCTION SERVICES" Or ARange(1).Value = "HEAVY & HIGHWAY" Or ARange(1).Value = "HEAVY CIVIL - SPS" Then
        If DRange Is Nothing Then
            Set DRange = ARange
        Else
            Set DRange = Union(DRange, ARange)
        End If
    End If
    Next ARange

    If Not DRange Is Nothing Then DRange.EntireRow.Delete

    With ws.Rows(X & ":" & .Rows.Count).Delete
    End With
End Sub

I put some code in from here How do I delete everything below row X in VBA/Excel?, but I am getting the

compile error Invalid or unqualified reference

The code worked before adding in this line

With ws.Rows(X & ":" & .Rows.Count).Delete

how would I go about deleting the rows behind the cleaned up data?

What do you mean by "it's not working"? That does not describe the problem. Please explain which line you are getting the error on and what specific error you are getting in your question. — braX
– braX, Commented Oct 26, 2017 at 22:49
Keep in mind that when you are deleting rows, you need to use a backward stepping indexed loop, not a for each loop, or the rows get all messed up. — braX
– braX, Commented Oct 26, 2017 at 22:52
You don't declare or assign X anywhere. Option Explicit 1, you 0, gg. — chillin
– chillin, Commented Oct 26, 2017 at 22:57
Read this answer : stackoverflow.com/questions/35042717/excel-vba-delete-rows — braX
– braX, Commented Oct 26, 2017 at 22:59

chillin · Accepted Answer · 2017-10-26 23:39:40Z

Option Explicit

Sub ConstructionTools()

'Dim ARange As Range
'Dim DRange As Range
Dim ws As Worksheet
'Dim wsB As Worksheet
'Dim filename As String

Set ws = Sheets("Data")
'Set wsB = Sheets("Macro")
'Set DRange = Nothing

Dim RowIndex as long
Dim Counter as long

With ws.Range("L1:L28000")

Dim RowsToDelete() as string
Redim rowstodelete(1 to .rows)

For rowindex = .rows to 1 step -1

Select case .cells(rowindex,1).value
Case "BUILDING CONSTRUCTION", "CONSTRUCTION SERVICES", "HEAVY & HIGHWAY" Or "HEAVY CIVIL - SPS"

Counter = counter + 1
Rowstodelete(counter) = .cells(rowindex,1).address
End select

Next rowindex

End with

If counter >0 then
Redim preserve RowsToDelete(1 to Counter)

With application
.screenupdating = false
.displayalerts = false
.calculation = xlcalculationmanual
 Ws.range(strings.join(RowsToDelete,",")).entirerow.delete
.screenupdating = true
.displayalerts = true
.calculation = xlcalculationautomatic
End if

End sub

Untested and written on mobile, sorry for bad formatting. Code attempts to add the addresses of all rows which need to be deleted to an array, and then tries to delete all added rows in one go.

Collectives™ on Stack Overflow

Deleting below a certain variable row in Microsoft Excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related