Skip Row If Cell Is Empty

[dirk1965]

I have the following vba code which is normalizing some data from a pivot:

Dim LastRow As Long
    Dim LastCol As Long
    
    eRow = 1
    Do While Application.CountA(ws.Rows(eRow).EntireRow) > 0
        eRow = eRow + 1
    Loop
    
    eCol = 1
    Do While Application.CountA(ws.Columns(eCol).EntireColumn) > 0
        eCol = eCol + 1
    Loop
    
    Set Rng = Range(Cells(1, 1), Cells((eRow - 1), (eCol - 1)))

    If Rng Is Nothing Then
    Else
        PTOutput.Application.ScreenUpdating = False
       
        PTOutput.Range("A1").Offset(0, 0) = Rng.Range("A1").Value
        PTOutput.Range("A1").Offset(0, 1) = Rng.Range("B1").Value
        PTOutput.Range("A1").Offset(0, 2) = Rng.Range("C1").Value
        PTOutput.Range("A1").Offset(0, 3) = "Date"
        PTOutput.Range("A1").Offset(0, 4) = "Value"
        i = 1
        For r = 1 To Rng.Rows.Count - 1
            For c = 3 To Rng.Columns.Count - 1
                PTOutput.Range("A1").Offset(i, 0) = Rng.Offset(r, 0).Value
                PTOutput.Range("A1").Offset(i, 1) = Rng.Offset(r, 1).Value
                PTOutput.Range("A1").Offset(i, 2) = Rng.Offset(r, 2).Value
                PTOutput.Range("A1").Offset(i, 3) = Rng.Offset(0, c).Value
                PTOutput.Range("A1").Offset(i, 4) = Rng.Offset(r, c).Value
                i = i + 1
            Next c
        Next r
        
        Application.ScreenUpdating = True

End If

How do I skip the whole row if PTOutput.Range("A1").Offset(0, 4) = "Value" is empty/blank?

Thanks

 

[mumps]

What cells does the rng refer for?

 

[dirk1965]

What cells does the rng refer for?

The rng refers to the range of the data that is being unpivot'd. That is determined by where the first blank row and first blank column is.
The If clause should check for null/blank cell in column e which has a the header name 'Value'.

I hope one of those is what you're asking about. If not, let me know.

Thanks

 

[mumps]

I have reworked you macro according to what I think you want to do. Hopefully, I have interpreted properly. See if this macro works for you.

Sub NormalizeData()
    Application.ScreenUpdating = False
    Dim Raw As Worksheet
    Dim PTOutput As Worksheet
    Dim Rng As Range
    Set PTOutput = Worksheets("Pivot")
    Set Raw = ActiveWorkbook.Sheets("Raw")
    Dim LastRow As Long
    LastRow = Raw.Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1, 1).Row - 1
    Dim LastCol As Long
    LastCol = Raw.Cells(1, Columns.Count).End(xlToLeft).Column - 2
    Dim r As Long, c As Long
    PTOutput.Range("A1:E1") = Array("OffsetA", "OffsetB", "OffsetAPI", "Date", "Value")
    For r = 2 To LastRow
        For c = 4 To LastCol
            If Raw.Cells(r, c) <> "" Then
                PTOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Raw.Cells(r, 1)
                PTOutput.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Raw.Cells(r, 2)
                PTOutput.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Raw.Cells(r, 3)
                PTOutput.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = Raw.Cells(1, c)
                PTOutput.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Raw.Cells(r, c)
            End If
        Next c
    Next r
    Set Rng = Range(Cells(1, 1), Cells(LastRow, LastCol))
    Rng.Name = "OffsetData"
    PTOutput.Activate
    Application.ScreenUpdating = True
End Sub

 

[dirk1965]

Sorry for the delay. Been on a business trip. This worked out perfectly. Thanks for the assist and I've done the "Thank You".

 

[mumps]

Glad to help.