data selection

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
Is there a way to select cells that only have data in them? trying to write a macro that selects data but the amount of rows changes depending on the day. thanks for any help I can get.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

If you will always have data in column A for example then something like

range("A1:A" & cells(rows.count,"A").end(xlup).row)

will select the data in column A.


Tony
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
What kind of data?

If you use:
Code:
    ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues + xlNumbers).Select
that will select all the cells on the active sheet that contain number values or text values. To select formulas would be a bit different.

What will you do with these cells once you select them? It's quicker, and uses less lines of code to perform and action on a range directly, rather than .Select it, and do something to .Selection.

Hope that helps!
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
thanks for your help. what i plan on doing is taking data entry inputs, copying it into a different format to print. the problem that i was having was that there are a different amount of entries day to day and i didn't was to copy blank cells.I will try to see if i can get one of these to work. I actually have 16 columns that have to be copied with each row.
thanks again.
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52

ADVERTISEMENT

TazGuy, I would like to try to use your code. I am new at this, how would I set this up in VB to run. Do I put it into a module?? I am really not sure. any help you could give me would be great.
thanks again

royboy531
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
acw, I tried to use your code and I get an COMPILE ERROR: invalid use of property. Could I be doing something wrong??
thanks for the help
royboy531
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

royboy531 said:
TazGuy, I would like to try to use your code. I am new at this, how would I set this up in VB to run. Do I put it into a module??

You could put it in a standard module, or in event code. It really depends on what you're trying to accomplish. If you can, you should incorporate it with other code, so you don't have extra modules hanging around that only do one thing.

Make sense?
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
TazGuy, I did get it to work. Do you know if there is a way to select all data and any empty cells that are in between. for instance if there is data in a1 and a3 it will grab a1, a2 and a3. I tried to use acw's code to see if it would do that but I couldn't get it to work. thanks for your help.
Royboy531
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Just add a .Select at the end:

Code:
Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

HTH
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
TazGuy, This did work but not quite the way I wanted to. lets say I have info from a1 to a7 and from b1 to b6 is there a way I can select data from a1 to b7 in a way that the row number may change. could by 10 rows or 15. I am not sure if this is clean and I don't know how to post an pic of worksheet but I could do my best if I need to explain further.[/list]
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,131
Members
412,305
Latest member
Mozz
Top