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

 

[dirk1965]

I added an If clause in the nested loop, but I'm getting a type mismatch error.
This is the line that the debug has an issue with:

If Len(Rng.Offset(r, c).Value) > 0 Then

Sub NormalizeData()
    
    Dim ws As Worksheet
    Dim PTOutput As Worksheet
    
    Dim Rng As Range

    Application.DisplayAlerts = False

    Set PTOutput = Worksheets("Pivot")
    Set ws = ActiveWorkbook.Sheets("Raw")
    
    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
                If Len(Rng.Offset(r, c).Value) > 0 Then
                    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
                End If
            Next c
        Next r
    Application.ScreenUpdating = True
End If

Debug.Print "LastRow = " & (eRow - 1)
Debug.Print "LastCol = " & (eCol - 1)

ws.Select
Set Rng = Range(Cells(1, 1), Cells((eRow - 1), (eCol - 1)))
Rng.Name = "OffsetData"
    
End Sub

Any idea why? Thx!

 

[mumps]

Try:

If Rng.Offset(r, c).Value <> "" Then

 

[dirk1965]

Try:

If Rng.Offset(r, c).Value <> "" Then

Thanks for the reply. I applied the code, but I'm now getting a runtime error 13; Type Mismatch

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[dirk1965]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

No worries. Here is the link to the file:

https://drive.google.com/open?id=0B4w20SQzW8pCZXFRUG5jSUpKQVU

The macro name is NormalizeData

Thanks

 

[mumps]

I'm having a little trouble following your code. Can you explain in words exactly what you are trying to accomplish? Use a few examples referring to specific cells, ranges and worksheets.

 

[dirk1965]

I'm having a little trouble following your code. Can you explain in words exactly what you are trying to accomplish? Use a few examples referring to specific cells, ranges and worksheets.

I'm normalizing the data from the Raw sheet. Initially, it looks for a first blank row and blank column to set a range. Then it takes all of the Date columns and unpivots them into a single Date column and a single Value. Once in the Pivot (really an unpivot), I'm writing to a csv file. If you click the blue Run Macro button, it will populate the Pivot sheet. You will notice that there are many rows where the Value column is null. I only want to write the rows out to the csv file where the Value column is populated.

 

[mumps]

When I click the button, I get an error on this line:

If Rng.Offset(r, c).Value <> "" Then

 

[dirk1965]

When I click the button, I get an error on this line:

If Rng.Offset(r, c).Value <> "" Then

Correct... that's what I was commenting about on my post. Not sure why I'm getting the mismatch error.

The If statement is there to attempt to do a Blank/Null check in the Value field. If its Blank/Null, then it should skip the row. If not, then proceed.