Get info from 1 coloum via vba

A

andymack

New Member
Joined
Aug 17, 2006
Messages
10
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
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.

Code: 
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!!!!!!!!!!
 
Upvote 0
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.
 
Upvote 0
Had a problem with the code above. It didnt display any names and didnt want to stop either!!! Can anyone please help me?
 
Upvote 0
Andy

Do you want to split out the data from a worksheet into seperate worksheets based on the criteria (ie name) in 1 column?
 
Upvote 0
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 :
Code: 
MsgBox a
to this :
Code: 
MsgBox items(a)

HTH

DominicB
 
Upvote 0
Dominic

An error on the 1,000,000th post?:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

BradleyS
  • Solved
Issue passing two parameters from sub to function to get back result
Replies
5
Views
188
RoryA
RoryA
R
  • Solved
Pass Worksheet to general Sub (or Function)
Replies
8
Views
227
MARK858
MARK858
A
  • Solved
COUNT multiple SUM COUNTIFS
Replies
6
Views
415
Peter_SSs
Peter_SSs
J
  • Question Question
Filter Function with OR being similar
Replies
3
Views
555
kevin9999
K
M
  • Question Question
Trouble removing formatting in vlookup area
2
Replies
14
Views
442
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,225,337
Messages
6,184,354
Members
453,227
Latest member
Slainte

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top