I have some raw data in a spreadsheet that came from another software. I
want to have a macro select all the cell range that contains information in
it so that I can have the macro format the fonts, cell frames, etc. How do I
have it search and select the columns and rows that contain the data?


--
Debra Ann

Hi Debra

You can try this if there are no empty rows and columns in the data

Select a cell in your data and use the shortcut
Ctrl * to select the current region



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Debra Ann" wrote in message ...
I have some raw data in a spreadsheet that came from another software. I
want to have a macro select all the cell range that contains information in
it so that I can have the macro format the fonts, cell frames, etc. How do I
have it search and select the columns and rows that contain the data?


--
Debra Ann

Oops in code

ActiveCell.CurrentRegion.Select

Or for the usedrange

ActiveSheet.UsedRange.Select



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Debra Ann" wrote in message ...
I have some raw data in a spreadsheet that came from another software. I
want to have a macro select all the cell range that contains information in
it so that I can have the macro format the fonts, cell frames, etc. How do I
have it search and select the columns and rows that contain the data?


--
Debra Ann

Several ways. Is all the data in one contiguous range? Are there any other
cells in the sheet you DON'T want to select? Does it always come into Excel
using the same top left cell?

Assume it always comes in in a contigous range, staring in A1, then use

activesheet.range("a1").currentregion.select

Having said this - you don't need to select a range in order to act on it.
You could just do this

Sub Fmt()
Dim rng As Range
Set rng = ActiveSheet.Range("a1").CurrentRegion
With rng
.Font.Bold = True
.Interior.ColorIndex = 5
End With
End Sub





"Debra Ann" wrote:

I have some raw data in a spreadsheet that came from another software. I
want to have a macro select all the cell range that contains information in
it so that I can have the macro format the fonts, cell frames, etc. How do I
have it search and select the columns and rows that contain the data?


--
Debra Ann

Thanks to both of you.

--
Debra Ann


"Duke Carey" wrote:

Several ways. Is all the data in one contiguous range? Are there any other
cells in the sheet you DON'T want to select? Does it always come into Excel
using the same top left cell?

Assume it always comes in in a contigous range, staring in A1, then use

activesheet.range("a1").currentregion.select

Having said this - you don't need to select a range in order to act on it.
You could just do this

Sub Fmt()
Dim rng As Range
Set rng = ActiveSheet.Range("a1").CurrentRegion
With rng
.Font.Bold = True
.Interior.ColorIndex = 5
End With
End Sub





"Debra Ann" wrote:

I have some raw data in a spreadsheet that came from another software. I
want to have a macro select all the cell range that contains information in
it so that I can have the macro format the fonts, cell frames, etc. How do I
have it search and select the columns and rows that contain the data?


--
Debra Ann

I do this routinely. How I handle it is by pre-formatting the range(s)
receiving the data so Excel does it "instantly" as the data populates the
cells. I use Conditional Formatting as required for things different to the
standard format I want in the ranges.

I'm not saying this is a better solution for random data import. Since I do
this "routinely", I insert a pre-formatted sheet that I keep in my Templates
folder so it's available from the sheet tab Insert... dialog. By default, I
think, Excel imports values only, and the data receives whatever formatting
is in already place.

Regards,
GS


"Debra Ann" wrote:

I have some raw data in a spreadsheet that came from another software. I
want to have a macro select all the cell range that contains information in
it so that I can have the macro format the fonts, cell frames, etc. How do I
have it search and select the columns and rows that contain the data?


--
Debra Ann