Select All Cells Containing Data

[Guest]

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.

 

[stevebriz]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks, Tom, that was just what I needed.

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