Get info from 1 coloum via vba

[andymack]

hey. I've got an excel sheet which contains all the office phone data. I need to be able to give a copy to the phone owners. To do this, I wanted to get all the names out of a column. The column is column C. I then planned on using this to identify which rows to put into the new workbook. However I am having problems trying to get all the names out of column C into a variable(s). I tried an array and single variables but my problem is the number of names changes regularly. I guess I could make a large array (100 would probably do) but I'm still left with the problem of trying to get the data from column C into the Array.

Any help you guys can give would be very usefull!

Andy

 

[DominicB]

Good morning andymack

Don't know if this is what you're looking for. It's a very simple piece of code that will list all the info in column C - starting at line 1 - up until the last line, storing in an array. You've not said what you want to do when you've captured it so this lists each item in a MsgBox.

Sub test()
Dim items()
n = Range("C65536").End(xlUp).Row
ReDim items(n)
For a = 1 To n
items(a) = Range("C" & a).Value
Next a
For a = 1 To n
MsgBox a
Next a
End Sub

HTH

DominicB



EDIT : This is post number 1,000,000 to MrExcel!!!!!!!!!!

 

[andymack]

kl. Once I have the data I wanted to copy rows out of the original table and into new ones so that, where the original had say Tom **** and Harry, each new worksheet would have just 1 of the 3 people. Therefore I could give Tom, **** and Harry an excel sheet each that would list only their phone bill, and making it impossible for tom to know ****'s phone bill etc.

 

[andymack]

Had a problem with the code above. It didnt display any names and didnt want to stop either!!! Can anyone please help me?

 

[erik.van.geit]

Hi,

a sample of your data + desired result could clarify what you're trying to achieve

kind regards,
Erik

 

[Norie]

Andy

Do you want to split out the data from a worksheet into seperate worksheets based on the criteria (ie name) in 1 column?

 

[DominicB]

Hi andymack

All the items are put into the array. An error on my part means the MsgBox doesn't display the array properly. Change this line :

MsgBox a

to this :

MsgBox items(a)

HTH

DominicB

 

[Norie]

Dominic

An error on the 1,000,000th post?

 

[DominicB]

Hi Norie

I know, I know...

DominicB