Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple columns to one with separator, omitting empty col | Excel Discussion (Misc queries) | |||
2007 Worksheet, Hidden Columns, .CSV Format Saves Hidden Column Da | Excel Discussion (Misc queries) | |||
Copy and Paste with hidden columns remaining hidden | Excel Discussion (Misc queries) | |||
Hidden Columns No Longer Hidden after Copying Worksheet? | Excel Discussion (Misc queries) | |||
hidden rows & columns slow file open | Excel Discussion (Misc queries) |