One way:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String

Set wks = Worksheets("Sheet1")

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

myFormula = ),A2:A@@@))"

myFormula = Replace(myFormula, "@@@", LastRow)

MsgBox .Evaluate(myFormula)
End With

End Sub



kazoo wrote:

This seems to work great. Another question, if instead of a =MIN(A2:A???)
function, I want to use an array formula (CTRL+SHIFT+ENTER) , where ??? is
the end of the column to exclude the number -273.15 in finding the min, how
do I translate this to VB?
=MIN(IF(ISNUMBER(A2:A???)*(A2:A???<-273.15),A2:A???))

"reklamo" wrote:

Hi kazoo

Try following code:

Sub ReadCSVMinMax()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActWB = ActiveWorkbook.Name
ActSheetRow = 1
ActSheetCol = 1
CSVDir = "D:\ExcelTest\"
MaxNoOfCSVFile = 3
For i = 1 To MaxNoOfCSVFile
' Write CSV-filename, min and max into active sheet
Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow, ActSheetCol).Value =
"File" & i
Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 2,
ActSheetCol).Value = "Min"
Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 3,
ActSheetCol).Value = "Max"
' Open CSV-file
Workbooks.Open Filename:=CSVDir & "File" & i
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=True, Comma:=True, Space:=False, Other:=False
UsedRows = ActiveSheet.UsedRange.Rows.Count
UsedCols = ActiveSheet.UsedRange.Columns.Count
' Select and transfer data
For cols = 3 To UsedCols
ActSheetCol = ActSheetCol + 1
' Header of Column
Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 1,
ActSheetCol).Value = ActiveSheet.Cells(1, cols).Value
' Min of Column
Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 2,
ActSheetCol).Value =
Application.WorksheetFunction.Min(ActiveSheet.Rang e(Cells(1, cols),
Cells(UsedRows, cols)))
' Max of Column
Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 3,
ActSheetCol).Value =
Application.WorksheetFunction.Max(ActiveSheet.Rang e(Cells(1, cols),
Cells(UsedRows, cols)))

Next cols
' Close CSV-file
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
' Define Row and Col for next data
ActSheetRow = ActSheetRow + 5
ActSheetCol = 1
Next i
End Sub


regards
reklamo



"kazoo" wrote:

I have multiple *.csv files that I want to find the min/max of each data
column and write to an excel worksheet. The original *.csv data from the
file does not need to be written out to the worksheet, only the min/max
values for each column.

For example, here is some sample data for *.csv File #1.

Example of first few lines of *.csv File1
%TIME,DATE_TIME,Data A,Data B,Data C,Data D
1202626798.174,"02/01/08 11:00:03 MST",4368,-2.29,33.6,9.216e-07
1202626803.174,"02/01/08 11:00:08 MST",4752,.51,44.2,9.216e-06
1202626808.174,"02/01/08 11:00:13 MST",3992,1.23,-40.6,8.07e-07

Ideally, it would look something like this in the Excel spreadsheet:

File1
Data A Data B Data C Data D
Min 3992 -2.29 -40.6 8.07e-07
Max 4752 1.23 44.2 9.216e-06

Again, subsequently would follow File 2, File 3, etc. All data could be
written to the same worksheet.

Thanks for your help!


--

Dave Peterson