Rewriting Scripts with Old Excel UDF

[litlmike]

I am wondering if there is a way to read all of the cells in only two columns with the new Excel UDF?  I was thinking something like this might work, but alas it only read one column and it even included all of the blank cells in the "C" column.  Is there another way to do this with the new UDF? 

$aArray = _Excel_RangeRead($oWorkbook, $oWorkbook.ActiveSheet, "C:C,K:K")

Edited September 25, 2014 by litlmike

[water]

You could either use UsedRange

$aArray = _Excel_RangeRead($oWorkbook, $oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.UsedRange)

to read all used cells of the worksheet and only process columns C and K from the returned array or use something like:

$iLastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
$sRange = "C1:C" & $iLastRow & ";K1:KC" & $iLastRow
$aArray = _Excel_RangeRead($oWorkbook, $oWorkbook.ActiveSheet, $sRange)

This only works when the used range starts with the first row in the Excel sheet.

[litlmike]

Thanks for the reply.  I did not know about the UsedRange.Rows.Count option and that helps a lot.  I was able to get the range to work when I isolate it to just 

$sRange = "C1:C" & $iLastRow

But, as soon as I try your semicolon trick it fails to produce any array.

$sRange = "C1:C" & $iLastRow & ";K1:K" & $iLastRow

Thoughts?

[water]

Can't test at the moment but could you replace ";" with "," and try again?

[litlmike]

No dice.  That was actually my first attempt, and I retried it just in case, but still no luck.

[water]

I will check as soon as I return to my office.

Edited September 26, 2014 by water

[GreenCan]

No dice.  That was actually my first attempt, and I retried it just in case, but still no luck.

This might help. I often use macro to check the function syntax...

Do as follows to simply check your separator

Open Macro editor and start recording

now select a few cells, press CTRL and select a few other cells, release CTRL

Look at the result in your macro editor.  Mine looks like this for 3 selections:

Range("F7:G9,I7:J9,L7:M9").Select

GreenCan

[water]

I fear that the Excel methods I use in the function do not support multiple ranges.

So you could either read the columns by calling _Excel_RangeRead twice or simply read the whole WorkSheet in one go.