select only where text exists
 

I need to write a macro that selects all the text in col A and B where data
exits. I dont want to copy the entire col A and B because i then have to
paste that data to a spreadsheet appending it to the existing data.
Spreadsheet 1 may have 1-100 + or - rows in it. It will vary. I then want to
add it to the bottom of a master spreadsheet. I am using this spreadsheet
somewhere else as a VLOOKUP. Any help on copy and pasteing the data would be
appreciated!

select only where text exists
 

Here's one way. Use the "specialcells" option to select cells that
contain text in cols A & B, for example

Set rngcolA = Activesheet.usedrange.columns(1)
Set rngColB = Activesheet.usedrange.columns(2)

With rngcolA.specialcells(clcelltypeconstants)
..copy destination:="your new sheet"
End with

With rngcolB.specialcells(clcelltypeconstants)
..copy destination:="your new sheet"
End with

(note: untested code)

This will limit the operation to the text cells in the "dirty" area of
the worksheet, making your macro run much faster.

HTH,
JP


On Nov 19, 6:27 pm, Daniel M
wrote:
I need to write a macro that selects all the text in col A and B where data
exits. I dont want to copy the entire col A and B because i then have to
paste that data to a spreadsheet appending it to the existing data.
Spreadsheet 1 may have 1-100 + or - rows in it. It will vary. I then want to
add it to the bottom of a master spreadsheet. I am using this spreadsheet
somewhere else as a VLOOKUP. Any help on copy and pasteing the data would be
appreciated!

select only where text exists
 

Of course that should have been "xl" not "cl"

--JP

On Nov 19, 6:44 pm, JP wrote:
Here's one way. Use the "specialcells" option to select cells that
contain text in cols A & B, for example

Set rngcolA = Activesheet.usedrange.columns(1)
Set rngColB = Activesheet.usedrange.columns(2)

With rngcolA.specialcells(clcelltypeconstants)
.copy destination:="your new sheet"
End with

With rngcolB.specialcells(clcelltypeconstants)
.copy destination:="your new sheet"
End with

(note: untested code)

This will limit the operation to the text cells in the "dirty" area of
the worksheet, making your macro run much faster.