Omitting Hidden Columns from a .CSV File
 

With the advent of Excel2007, with its increased WS size, it would be
advisable to stop hard coding row and column counts into code.
Use Cells.Rows.Count/Cells.Columns.Count instead.

Having said that, could you not just work on the .UsedRange or
..CurrentRegion ?

NickHK

"Barry" wrote in message
...
Martin:

This looks like exactly what I need. Thank you for your quick response.
--
Barry Carroll

(Cleverly disguised as a responsible adult)
---------
PSC Scanning, Inc. assumes no responsibility whatsoever for any statements
made by me. I''m entirely on my own.


"Martin Fishlock" wrote:

Barry,

The hidden property can be used in VBA have a look at the following:

Option Explicit

Sub copyandsavecsv()
Dim ws As Worksheet
Dim i As Long
Dim szFileName As Variant

ActiveSheet.Copy ' does it on the active sheet
Application.ScreenUpdating = False
Set ws = ActiveSheet

ws.Cells.Copy
ws.Cells.PasteSpecial Paste:=xlPasteValues

For i = 36636 To 1 Step -1 ' delete hidden rows
If ws.Rows(i).Hidden = True Then
ws.Rows(i).Delete
End If
Next i

For i = 256 To 1 Step -1 'delete hidden columns
If ws.Columns(i).Hidden = True Then
ws.Columns(i).Delete
End If
Next i
' save it unless cancel pressed.
szFileName = Application.GetSaveAsFilename( _
fileFilter:="CSV Files (*.csv), *.csv")
If szFileName < False Then
ActiveWorkbook.SaveAs Filename:= _
szFileName, FileFormat:=xlCSV
End If
ActiveWorkbook.Close False ' and close the csv file
Application.ScreenUpdating = False
Set ws = Nothing
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Barry" wrote:

Greetings:

When saving a worksheet as a .csv file, I want to control which
columns are
saved and which are omitted. Hidden columns (and rows) are not
printed. Can
the "hidden" property be accessed by VBA and used to control the save
process?

Thanks in advance.
--
Barry Carroll

(Cleverly disguised as a responsible adult)
---------
PSC Scanning, Inc. assumes no responsibility whatsoever for any
statements
made by me. I''m entirely on my own.