arrays

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

  • Home
  • Courses
  • Promotions
  • Schedule
  • Formats
  • Our Clients

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Arrays

Arrays

resolvedResolved · Medium Priority · Version 2016

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

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

RE: Arrays

Hi Jens

Thanks very much. This is very helpful and clear.

Diane


 

Excel articles

Please browse our web site to find out more about
excel training class and other training courses.

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


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.08 secs.