determine max number of columns in a spreadsheet

[sjharri]

I have a macro that extracts data from different spreadsheets and puts
it into csv files. I need to insert a row into the created csv file as
a header row, this will be sequentially populated (e.g. 1,2,3,4....).
Problem I have is that I am using one macro to extract data from a
number of spreadsheets (users choice) and the number of columns varies
between spreadsheets.

I need the macro to be able to check each spreadsheet and identify the
max number of columns, remembering null values are allowed, and then
insert and sequentially number a new row at the top of the file, up to
the last column in the file.

For example,

File 1 - contains 6 columns therefore a top row would be inserted and
numbered 1-6
File 2 - contains 2 columns therefore a top row would be inserted and
numbered 1-2

Is this possible? Thanks
Steve

 

[Guest]

Dim LastCol As Long

LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column
- 1
Rows("1:1").Insert Shift:=xlDown
Range("A1").Select
ActiveCell.Value = 1
Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)),
Type:=xlFillSeries