CODEWITHSUNDEEP
excelvbaexport-to-csv
Icarus
Icarus

Reputation: 455

remove hidden column save excel range to csv

I did code to save excel range to csv
but its getting hidden columns also can somebody help to remove hidden column?

'Sub to select range from excel and save it as CSV
'Added code for paste special    
Private Sub Main()
    Dim sFullFilePath As String
    Dim selectedRange As Range

    sFullFilePath = "C:\MyFileName.csv"
    Set selectedRange = Application.InputBox("Select a range", "Get Range", Type:=8)

    RangeTOCsv sFullFilePath, selectedRange
End Sub

Private Sub RangeTOCsv(sFullFilePath As String, selectedRange As Range)
    Dim workBook As workBook
    Application.DisplayAlerts = False

    selectedRange.Copy
    Set workBook = Workbooks.Add
    With workBook
        .Sheets(1).Select
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
        .SaveAs sFullFilePath, xlCSV
        .Close
    End With
End Sub

Upvotes: 1

Views: 281

Answers (1)

mielk
mielk

Reputation: 3940

You need to modify RangeToCsv procedure like below:

Private Sub RangeToCsv(sFullFilePath As String, selectedRange As Range)
    Dim rng As Excel.Range
    Dim Workbook As Workbook

    Application.DisplayAlerts = False

    Set rng = selectedRange.SpecialCells(xlCellTypeVisible)
    Set Workbook = Workbooks.Add
    With Workbook
        Call rng.Copy
        .Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
        .SaveAs sFullFilePath, xlCSV
        .Close
    End With
End Sub

Before the selected range is pasted into new workbook, it is being filtered by function SpecialCells with parameter Type set to xlCellTypeVisible.

After this operation, variable rng stores all the visible cells from the original selectedRange range.

Upvotes: 1

Related Questions