Arrays

Resolved · Medium Priority · Version 2016

Sun 1 Sep 2019, 09:01 Delegate Diane said...

Diane has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course

Arrays

I have used this command successfully to move the contents of an array to a sheet (all rows, only 3 columns). I want to then repeat this, opening two other workbooks and copying them into an array. but for these subsequent workbooks I don't want the first row in the array, as it is a title row and I already have that from my first workbook. How do I modify this line to get all the array except the first row please?

Range(sStartCell).Resize(UBound(NamesArray, 1), 3) = NamesArray

Mon 2 Sep 2019, 15:09 Trainer Jens said...

RE: Arrays

Hi Diane,

I hope that you are fine.

If you are using CurrentRegion to feed your array you should use an Offset to avoid getting the header row in your array.

NamesArray=Sheets(1).Currentregion.Offset(1,0)

You can also empty your array by using a double For Next loop.

For iRow=2 to Ubound(NamesArray,1)
For iCol=1 to 3

"Your start cell"=NamesArra(iRow,iCol)
Next iCol
Next iRow

You will also need to navigate "your start cell" by using the counters from the two loops. You can use Cells(iRow, iCol), but you will need to amend this so it fit to your needs.

I hope this makes sense.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 3 Sep 2019, 09:43 Delegate Diane said...

RE: Arrays

Hi Jens

Thanks very much. This is very helpful and clear.

Diane

Excel tip:

Counting Blanks

Some times you want to check if there are cells missing data in your range. You can use the COUNTBLANK FUNCTION to acheive this. It is =COUNTBLANK(Range). Note Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted.

View all Excel hints and tips

Arrays

Arrays

RE: Arrays

RE: Arrays

Excel articles

Excel tip:

Counting Blanks