Avatar of shilpi2
shilpi2 asked on

Picking column headings

Hi Experts,

I have an excel sheet and I want to pick up headings from row 4,row 5 column G onwards in an array in visual basic.
Also the array I create has to be dynamic as I do not know beforehand how many columns to go beyond G. I have to stop before the point it becomes blank
How do I do it?

Thanks

Shilpi
Visual Basic ClassicVisual Basic.NETVB Script

Avatar of undefined
Last Comment
StephenJR

8/22/2022 - Mon
StephenJR

Can you provide more detail. Do you want the values in G4, G5, H4, H5 etc (until you find a blank) in an array? Or G4, H4 etc in one array and G5, H5 in another? Or G4, H4, G4, G5...?
ASKER
shilpi2

The first one. G4,G5,H4,HG etc in one array until i come across a blank
ASKER CERTIFIED SOLUTION
StephenJR

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
shilpi2

Hi,

I have to write this code in another worksheet. How do I modify this code so that it loops another worksheet.

Thanks
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
StephenJR

Adjust sheet name as required:
Sub x()

Dim v(), c As Long, i As Long

c = 7

Do Until IsEmpty(Sheet("Name").Cells(4, c))
    i = i + 2
    ReDim Preserve v(1 To i)
    v(i - 1) = Sheet("Name").Cells(4, c)
    v(i) = Sheet("Name").Cells(5, c)
    c = c + 1
Loop

End Sub

Open in new window