Yes, the problem is, I am copying the data from a named range to the other sheet with vba, and the named range will leave formula values that you dont see unless you click on the cell, and as long as those ghost values are there, even though they are not in column b, it wont delete the row.
I replace the sheet in this spreadsheet called "Import" each week with new data, sometimes there are 200 rows, sometimes less, or more. So the sheet "BU02Data, is the dynamic range, it pulls data from "Import" and makes various calculations, and if this weeks row count is less than the previous, it leaves the ghost data. And in the section "Copy BU02Data to Atlas Upload, it will carry those blank lines to GeneralJournal which bombs my upload. Here is a my entire code string:
Sub ProcessPayroll02()
'Clear Payroll Data BU 02
Sheets("BU02Data").Select
Range("A1:G500").Select
Selection.ClearContents
Range("A1").Select
'Copy Data from Import to BU02Data
Dim Ws As Worksheet
Set Ws = Worksheets("BU02Data")
With Worksheets("Import")
.Range("A1:G1").AutoFilter 6, "500-*"
.AutoFilter.Range.Offset().Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset()
.AutoFilterMode = False
End With
'Copy BU02Data Data to Atlas Upload
Sheets("GeneralJournal").Select
Range(Range("B17:M17"), Range("B17:M17").End(xlDown)).Select
Selection.ClearContents
Sheets("BU02Data").Select
Range(Range("H1:s1"), Range("H1:S1").End(xlDown)).Copy
Worksheets("GeneralJournal").Range("B17").PasteSpecial Paste:=xlPasteValues
'Clear Clipboard
Application.CutCopyMode = False
On Error Resume Next
Sheets("GeneralJournal").Select
With Range("Q17:Q" & Cells(Rows.Count, "B").End(xlUp).Row)
.Formula = "=IF([@AccountType]=""Ledger"",CONCATENATE([@[Main account]],""-"",[@BusinessUnit],""-"",[@Department],""-"",[@CostCenter],""-"",[@CIP],""-""),IF(OR([@AccountType]=""Customer"",[@AccountType]=""Vendor"",[@AccountType]=""Fixedassets""),SUBSTITUTE([@[Main account]],""-"",""\-"",1),[@[Main account]]))"
End With
'Delete Blank Rows if No Data in cells in Column B
On Error Resume Next
Sheets("GeneralJournal").Select
Range("B17:B500").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B17").Select
End Sub