Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Up down arrow keys do not select cells if select locked cells unch | Excel Discussion (Misc queries) | |||
How do you select all text/data within all the cells of a column? | Excel Discussion (Misc queries) | |||
Select data from cells | Excel Discussion (Misc queries) | |||
Select Cells In Column that have data | Excel Programming | |||
Macro Select only cells with data in them | Excel Programming |