Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

VBA delete 2 columns before export

hi,

before i export i like to delete column p and q in sheet import
this gives me an error  ActiveSheet.Columns("P:Q").Delete
Any idea!?

Sub csv_Export()
    Dim lastColumn As Integer
    Dim lastRow As Integer
    Dim strString As String
    Dim i As Integer, j As Integer
    Dim UD As String
    Dim Bereich As Range
    
    Dim filename As Variant
   'user will be prompted to choose file name and location while the extension is fixed
    filename = Application.GetSaveAsFilename(FileFilter:="CSV files (*.csv), *.csv")
    UD = filename
    
    Worksheets("IMPORT").Activate
    lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    

ActiveSheet.Columns("P:Q").Delete
   


    Open UD For Output As #1

    For i = 1 To lastRow
        Cells(i, 1).Select
        strString = ""
        For j = 1 To lastColumn
            If j <> lastColumn Then
                strString = strString & Cells(i, j).Value & ";" ' Use semicolon instead of pipe.
            Else
                strString = strString & Cells(i, j).Value
            End If
        Next j
        If Len(Trim$(Replace(strString, ";", ""))) > 0 Then
        Print #1, strString
        End If
     Next i

    Close #1
End Sub

Open in new window

0
Mandy_
Asked:
Mandy_
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It works on my spreadsheet.

Are those columns Protected?

Alternatively, try:

Worksheets("IMPORT").Columns("P:Q").Delete

Open in new window

0
 
NorieData ProcessorCommented:
Try using Range instead of columns.
Sheets("Import").Range("P:Q").Delete

Open in new window

0
 
aikimarkCommented:
what is the error number/message you are seeing?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mandy_Author Commented:
Dear phillip,

you solved it but only one thing the output.csv has right now a blank row between like this
how can I prevent it?

NONBM;22.09.2014 11:45:00;New;;;

NONBM;22.09.2014 11:45:01;New;;;

NONBM;22.09.2014 11:45:01;New;;;
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can you post your spreadsheet please.
0
 
Mandy_Author Commented:
another problem occurs. Runtime error 13  "Typ incompatible" and the string is marked see pic

vba-export.png
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
And what is the value of Cells(i,j).Value for this specific instance? (Hover over it to find out.)
0
 
Mandy_Author Commented:
i found the error .  one cell has #nv. everything fine now. thank you so much
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now