0

I want to output a bill of materials (BoM) to CSV in a precise format ready for import. I have a functioning solution but it adds blank lines.

Due to the intended way of working, and the precise format of the output, I've had to translate the data across three sheets in Excel.

Firstly using an advanced filter copying to Sheet2, this copies the rows I need but also all of the columns which I don't need, additionally, the columns aren't in the required sequence.

Secondly copying only the required cells using =IF(Sheet2!C20="","",Sheet2!C20) into the sheet "StockIT", in the correct order ready for export. The formula copies from Sheet2 and IF null displays nothing but this hasn't stopped the lines being present in the .csv.

The problem I have is that to allow for different length BoMs I've entered 100 rows of these Excel formulas which are being seen when creating the .csv and turned into blank lines as: ,,,,,,, meaning every .csv output is 100 lines long.

How can I remove them?

Sub test()
    
    Application.DisplayAlerts = False
    
    Dim strSourceSheet As String
    Dim strFullname As String
    
    strSourceSheet = "StockIT"
    strFullname = ("C:\Users\AlexA\Desktop\") & Range("B1").Text
    
    ThisWorkbook.Sheets(strSourceSheet).Copy
    ActiveWorkbook.SaveAs Filename:=strFullname, FileFormat:=xlCSV, CreateBackup:=True
    ActiveWorkbook.Close
    
    Application.DisplayAlerts = True
    
End Sub

OP's solution:
"I've added the code to my original post, it runs with no warnings."

Sub test()

    'Sub HideRowsUsingRanges()
    Dim cell As Range, HideRows As Range
    Dim StartRow As Long, EndRow As Long, Col As Long
    
    'TARGET RANGE
    Col = 9
    StartRow = 3
    EndRow = 100
    'TARGET RANGE
    
    Dim sh As Worksheet
    Set sh = Sheets("StockIT")
    
    Dim r As Range
    'Saving the target range
    Set r = sh.Cells(StartRow, Col).Resize(EndRow)
    
    'Looping through each cell of the range
    For Each cell In r
        'If val or next val is empty
         If Cells(cell.Row, Columns.Count).End(xlToLeft).Column = 1 And Cells(cell.Row, Columns.Count).End(xlToLeft) = "" Then
         'If cell.Value = "" And cell.Offset(1).Value = "" Then
            'Add the corresponding row to HideRows range
            'Union causes an error if HideRows is nothing, so the first iteration cant use Union
            If HideRows Is Nothing Then
                Set HideRows = cell
            Else
                Set HideRows = Union(HideRows, cell)
            End If
        End If
    Next cell
    
    'Delete the gathered rows
    If Not HideRows Is Nothing Then HideRows.EntireRow.Delete
'End Sub


'This works

Application.DisplayAlerts = False

Dim strSourceSheet As String
Dim strFullname As String

strSourceSheet = "StockIT"
strFullname = ("C:\Users\AlexA\Desktop\") & Range("B1").Text

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close

Application.DisplayAlerts = True

End Sub
Community
  • 1
  • 1
Alex123
  • 1
  • 4
  • When you're copying the data, you can trim the range by dynamically deciding the last row. [Example](https://stackoverflow.com/a/71310/14608750) – Toddleson Jun 02 '21 at 13:02
  • When you have the data you can delete blank rows manually like [this](https://www.businessinsider.com/how-to-remove-blank-rows-in-excel). – Toddleson Jun 02 '21 at 13:04
  • Or if you want to do it with VBA you can do it like [this](https://stackoverflow.com/a/67780629/14608750). But instead of `.Hidden = True` at the end, you'll use `.Delete`. And you just need to adjust the If Statement to check if the row is blank instead of one specific column. I would suggest `If Cells(i, Columns.Count).End(xlToLeft).Column = 1 And Cells(i, Columns.Count).End(xlToLeft) = "" Then`. Which checks if there is any data anywhere in the entire row. – Toddleson Jun 02 '21 at 13:07
  • toddleson, thanks for your input. I'm not having much luck, I've got runtime 1004 error with your, If Cells line highlighted. Any ideas? – Alex123 Jun 02 '21 at 13:48
  • In my comment, I used `i` as the row argument. Did you replace it with the actual iterated variable in your loop? – Toddleson Jun 02 '21 at 13:50
  • Ps) The step through each cell in the link is causing problems because row 1 is 8 columns and the remaining rows are 4 columns – Alex123 Jun 02 '21 at 13:52
  • Both methods in the answer I linked loop though every row of one column within the specified range. If you're using the array method, you need to replace `i` with `x + StartRow - 1` and if you're using the range method, you need to replace `i` with `cell.row` – Toddleson Jun 02 '21 at 13:58
  • I went with the range method, and have tried replacing the i with cell.row – Alex123 Jun 02 '21 at 14:05
  • Edit your post with what you have written and I'll take a look. Include details of the error and what line you're encountering it. – Toddleson Jun 02 '21 at 14:08
  • It runs but doesn't delete the rows, I added the .delete as advised, is this correct?: If Not HideRows Is Nothing Then HideRows.EntireRow.Delete – Alex123 Jun 02 '21 at 14:08
  • 2
    Ok I've added the code to my original post, it runs with no warnings. Thanks for your help, it's very much appreciated. – Alex123 Jun 02 '21 at 14:11
  • Since you're using VBA anyway, you might find it easier to ditch the formulas and pick up the data directly from wherever is the source sheet, then filter and re-arrange as you go. Once you have the data arranged as you want in a 2D array you can put it on a sheet for export to CSV, or write it directly to a file. – Tim Williams Jun 02 '21 at 23:34
  • @TimWilliams now that I've got this working and have proved the viability of the macro to importable format I am attempting to extract the data directly from one sheet. I'll need my macro to ask for 4 pieces of data to be entered into a text box, then based on an IF not zero function, transpose the data into the correct order, then saving into a new .csv file. Probably beyond me but I'm going to have a go! – Alex123 Jun 08 '21 at 08:25
  • 1
    @Toddleson I defined my range of rows to delete by using a count function in the spreadsheet and used that value to define the range for removal. Thanks for all of your help. – Alex123 Jun 08 '21 at 08:28

0 Answers0