CODEWITHSUNDEEP
vbaexcel
Robillard
Robillard

Reputation: 117

VBA Insert rows at certain point in data

I have this pivot table I am inserting above my data. The data can be different lengths and so the pivot table and be different lengths. So what I do is put the data a little ways down on the sheet starting at say maybe row 30. Then I insert my pivot table up top. What I do next is I then have some code to delete all of the blank rows in between my data and the pivot table. 

sub foo()
    dim r As Range, rows As Long, i As Long
    Set r = ActiveSheet.Range("A1:Z50")
    rows = r.rows.Count
    For i = rows To 1 Step (-1)
        If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
    Next
End Sub

This code will delete all of the blank rows and seems to work correctly. Since that puts everything together, what I want to do then is just put 2 blank rows in between my pivot table and my data but I don't know how to figure out where my pivot table ends or where my data begins.

enter image description here

So I want it to look like that ^^^^ , but right now Row 7 and 8 are not there and my data is pushed up onto my pivot table. Any help is appreciated please!

I was thinking someone like using Rows.Count + 1 or something to find where the pivot table ends. But I don't know how it would determine that since there is data in the row right after. SO maybe I need to find when there is data starting in say Column A then Insert the rows right before that. Thank you in advance for the help!

Upvotes: 0

Views: 415

Answers (1)

Excelosaurus
Excelosaurus

Reputation: 2849

Try something along those lines:

Public Sub Answer()
    Dim dataTopRowIndex As Integer
    Dim ptBottomRowIndex As Integer

    With ActiveSheet 'Note: use your worksheet's CodeName here instead, e.g. With Sheet1 (Active[anything], Activate, Select, Selection: evil).
        dataTopRowIndex = .Cells(1, 1).End(xlDown).Row

        With .PivotTables("PivotTable1").TableRange1 'Replace PivotTable1 by your PT's name.
            ptBottomRowIndex = .Row + .Rows.Count - 1
        End With

        If (dataTopRowIndex - ptBottomRowIndex - 1) > 2 Then
            .Range(.Rows(ptBottomRowIndex + 1), .Rows(dataTopRowIndex - 3)).EntireRow.Delete
        End If
    End With
End Sub

Upvotes: 2

Related Questions