Select All Cells Containing Data
 

What command can I use in my program to select all of the cells in a sheet
containing data despite frequent variances in the number of rows with data? I
can't seem to find it anywhere.

Select All Cells Containing Data
 

ajvasel wrote:
What command can I use in my program to select all of the cells in a sheet
containing data despite frequent variances in the number of rows with data? I
can't seem to find it anywhere.

try:

Cells.Select

Select All Cells Containing Data
 

Activesheet.UsedRange.Select

although this may select a larger area than you would expect. It represents
the smallest rectangular area that contains all the cells that Excel
considers it necessary to record information about. All cells outside this
area are essentially virtual until you use them. This setting doesn't always
compress however, just because you might clear or delete some cells.

If you have a contiguous area of filled cells, you might look at

Range("A1").Currentregion.Select


If a single column can be used to determine the extent of the data

Range("A1",cells(rows.count,1).End(xlup)).Resize(, 10).Select

If all values will be only constants (or only formulas), you might be able
to use one of the Specialcells options. Obviously, the more that is known
about the spreadsheet, the better a method can be tailored to return an
accurate result.

--
Regards,
Tom Ogilvy



"ajvasel" wrote:

What command can I use in my program to select all of the cells in a sheet
containing data despite frequent variances in the number of rows with data? I
can't seem to find it anywhere.

Select All Cells Containing Data
 

Thanks, Tom - I am trying the first suggestion right now. My spreadsheet
contains data in columns A - V (roughly 2,000 rows of data starting on row
4). Just about every cell contains data but not all. The
Activesheet.UsedRange.Select appears to have worked just as I needed though.

Select All Cells Containing Data
 

that selects all the cells in the sheet for me - regardless of whether they
contain data or not.

--
Regards,
Tom Ogilvy


"stevebriz" wrote:


ajvasel wrote:
What command can I use in my program to select all of the cells in a sheet
containing data despite frequent variances in the number of rows with data? I
can't seem to find it anywhere.

try:

Cells.Select

Select All Cells Containing Data
 

To further expand on this question, is it possible to select all of the cells
of a specific column when some of the cells in said column are blank.

I tried selecting a range greater than the cells containing data and used
control-shift-up, but in the next section of the code where I add a border,
it added a border around the entire column.

(example):
Range("K4:K4000").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With

Thanks

Select All Cells Containing Data
 

Range("K4:K4000").Select
Range("K4", Cells(rows.count,"K").End(xlUp)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With


--
regards,
Tom Ogilvy

"ajvasel" wrote:

To further expand on this question, is it possible to select all of the cells
of a specific column when some of the cells in said column are blank.

I tried selecting a range greater than the cells containing data and used
control-shift-up, but in the next section of the code where I add a border,
it added a border around the entire column.

(example):
Range("K4:K4000").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With

Thanks

Select All Cells Containing Data
 

Thanks, Tom, that was just what I needed.

"Tom Ogilvy" wrote:

Range("K4:K4000").Select
Range("K4", Cells(rows.count,"K").End(xlUp)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With


--
regards,
Tom Ogilvy

"ajvasel" wrote:

To further expand on this question, is it possible to select all of the cells
of a specific column when some of the cells in said column are blank.

I tried selecting a range greater than the cells containing data and used
control-shift-up, but in the next section of the code where I add a border,
it added a border around the entire column.

(example):
Range("K4:K4000").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With

Thanks