II. METODO
2.4. Técnicas e instrumentos de recolección de datos, validez y
9.6.1 The VBA Equivalents Of Shift-Down And Ctrl-Down
The visual basic equivalents of these two manual actions are 'this is the same as ctrl-shift-down
Range(ActiveCell, ActiveCell.End(xlDown)).Select 'this is the same as ctrl-down
ActiveCell.End(xlDown).Select
Please note that you may not get the results you expected with the above. For example, if the cell below the active cell is empty, the statement will select to the next cell with an entry or to the last cell in the sheet if there is no cell with an entry below the active cell.
You can also use the above statements and assign the resulting range or cell to a range variable versus selecting the cell. Doing so makes your code faster as you are not doing a select.
Dim tempRange As Range
Set tempRange = Range(ActiveCell, ActiveCell.End(xlDown)) or
Dim tempRange As Range
Set tempRange = ActiveCell.End(xlDown)
9.6.2 Determining The Last Cell In A Column
To set the variable lastCell to the last cell in a column do the following. If the variable topCell has been set to the first cell in a column containing an entry and there are no blanks cells in the column until one reaches the last cell.
Set lastCell = topCell.End(xlDown)
The above approach works even if topCell is not on the active sheet.
If you are uncertain if topCell is really the top cell, or if there are blank cells in the column, then use one of the following approaches
if you know that there are no cells below row 1000 with entries and the active sheet is the sheet containing topCell then do this
Set lastCell = Cells(1001, topCell.Column).End(xlUp) If topCell is on a sheet other then the active sheet, then use this approach.
Set lastCell = topCell.Parent.Cells(1001, _ topCell.Column).End(xlUp)
If you are uncertain how many rows may have data then use the following approach to get the last entry in the last column. Note the period in front of the key word Cells in the third row of this example
Dim maxRow As Long
With topCell.Parent.UsedRange
maxRow = .Cells(.Cells.Count).Row + 1 End With
Set lastCell = topCell.Parent.Cells(maxRow, _ topCell.Column).End(xlUp)
9.6.3 Finding The Last Entry In A Column
Their are several different ways to find the last entry in a column
If you know a row number which will always be below the last entry in the column, and you know the column number, then the last cell can be found very easily:
Dim lastEntryCell As Range
'set variable equal to the last cell in column 3, assuming last cell is well 'above row 20000
Set lastEntryCell = Cells(20000, 3).End(xlUp)
If you're uncertain of a row well below the last entry, you can find it this way:
'declare a variable as long so this will work if more than 32,768 rows Dim lastRow As Long
Dim lastEntryCell As Range
'use the count property to return the index number of the last cell in the 'UsedRange, then return its row number
With ActiveSheet.UsedRange
'add 100 to the last used row to get a number well below the last row lastRow = .Cells(.Count).Row + 100
End With
'set variable equal to the last cell in column 3
Set lastEntryCell = Cells(lastRow, 3).End(xlUp)
If you've got many entries in a column, and no blanks until the last entry, then you can find the last cell by using code like the following:
Dim lastEntryCell As Range
Set lastEntryCell = Range("A1").End(xlDown)
Using End(xlDown) or End(xlUp) will cause the screen to scroll if used on the active sheet. To prevent this, use code like the following.
'store the scroll settings
Dim scrollCol As Integer, ScrollRow As Integer scrollCol = ActiveWindow.ScrollColumn
scrollRow = ActiveWindow.scrollRow 'set a range variable to the last cell
'<code that finds the last cell>
'apply the stored scroll settings
ActiveWindow.ScrollColumn = scrollCol ActiveWindow.scrollRow = scrollRow
9.6.4 Finding The Last Non-Blank Cell In A Column
assuming that variable "c" has been assigned to the column number in question and the active sheet is the one containing the column in question then
Dim cell As Range
Set cell = Cells(65536, c).End(xlUp) will do the trick.
If the column is on a sheet other than the ActiveSheet, then qualify cells with the sheet object:
Set cell = Sheets("some sheet name").Cells(65536, c).End(xlUp) and so forth.
You can also use this approach, which eliminates the need for you to put in a row number:
Dim cell As Range
Set cell = Cells(Cells.Rows.Count, c).End(xlUp)
9.6.5 Finding The Last Entry In A Row
Their are several different ways to find the last entry in a row
The following is probably the easiest way to find the last entry in a row:
Dim lastRowEntryCell As Range 'set variable equal to the last cell in row 3
Set lastEntryCell = Cells(3, 256).End(xlLeft)
If you've got many entries in a row, and no blanks until the last entry, then you can find the last cell by using code like the following:
Dim lastEntryCell As Range
Set lastEntryCell = Range("A1").End(xlRight)
Using End(xlDown) or End(xlUp) will cause the screen to scroll if used on the active sheet. To prevent this, use code like the following.
'store the scroll settings
Dim scrollCol As Integer, ScrollRow As Integer scrollCol = ActiveWindow.ScrollColumn
scrollRow = ActiveWindow.scrollRow 'set a range variable to the last cell
'<code that finds the last cell>
'apply the stored scroll settings
ActiveWindow.ScrollColumn = scrollCol ActiveWindow.scrollRow = scrollRow
9.6.6 Determining The Last Cell In A Row
To set the variable lastCell to the last cell in a row do the following If the variable topCell has been set to the first cell in a row containing an entry and there are no blanks cells in the row until one reaches the last cell.
Set lastCell = topCell.End(xlToRight)
The above approach works even if topCell is not on the active sheet.
If you are uncertain if topCell is really the first cell in the row, or if there are blank cells in the row, then use one of the following approaches
1) if you know that there are no entries in the last column of the worksheet and the active sheet is the sheet containing topCell then do this
Set lastCell = Cells(topCell.Row, 256).End(xlToLeft)
2) If topCell is on a sheet other then the active sheet, then use this approach.
Set lastCell =
topCell.Parent.Cells(topCell.
Row, 256) _ .End(xlUp)
9.6.7 Finding The Last Cell, Last Row, or Last Column
Here are some additional "last cell" examples:
'1) VERY Last in a worksheet:
LastRow = Rows.Count LastCol = Columns.Count
Set LastCell = Cells(LastRow, LastCol) '2) Last in Used Range
LastRow = ActiveSheet.UsedRange.Row
LastColumn = ActiveSheet.UsedRange.Column Set LastCell = Cells(LastRow, LastCol) ' or
Set LastCell = _
ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count) '3) Last Non-Blank Cell In A Column
Set LastCell = Range(Rows.Count, 2).Offset(1, 0).End(xlUp) 'where 2 indicates column 2 ("B")
'4) Last Non-Blank Cell In A Row
Set LastCell = Range(2, Columns.Count).Offset(0, 1).End(xlLeft) 'where 2 indicates row 2.
The following is still another example. In this case the user wanted to select from cell H2 to the last entry in the column
Range(Range("H2"), Range("H" & Rows.Count).End(xlUp)).Select In the above example, Rows.Count returns the number of the last row in the workbook.
End(xlUp) is the same as pressing the End button and then up arrow.
9.6.8 Selecting from the ActiveCell to the Last Used Cell
The following statement will select from the active cell to the last cell in a worksheet's used range:
With ActiveSheet.UsedRange
ActiveSheet.Range(ActiveCell, _
.Cells(.Cells.Count)).Select End With
Note that Cells is qualified with a period, which means that it is qualified by the With object, the active sheet's used range. The code .Cells.Count returns the number of cells in the used range.
.Cells(.Cells.Count)) returns the bottom right cell in the used range.
You can assign this range to an object variable, which you then use in your code instead of selecting it.
Dim someCells As Range With ActiveSheet.UsedRange
Set someCells = ActiveSheet.Range(ActiveCell, _ .Cells(.Cells.Count))
End With
9.6.9 Determining The Last Cell When Multiple Areas Are Selected
Selecting the last cell in a multiple area selection is not difficult - if you have a clear definition of what you mean by the last cell. For example, is it the last cell selected, the cell with the highest row number, or the highest column number, or some other combination, or is it the cell that is the intersection of the largest row and column number (an may not be part of the selected range)?
The following examples illustrate solutions to several of these possible last cell possibilities.
The following code returns the last selected cell, which may not be the one with the largest row or column number:
Dim Rng As Range, lastArea As Range, lastCell As Range Set Rng = Selection
'determine the last area selected
Set lastArea = Rng.Areas(Rng.Areas.Count) 'determine the last cell in the last area
Set lastCell = lastArea.Cells(lastArea.Cells.Count) MsgBox lastCell.Address
The following returns the last cell, which is the intersection of the largest row number and the largest column number. Typically, this cell is not in the selected range.
Dim lastCell As Range
Set lastCell = Selection.SpecialCells(xlLastCell) MsgBox lastCell.Address
The following returns the cell that has the largest row number, and is in the right most column:
Dim lastCell As Range Dim rng As Range Dim area As Range Dim tempR As Range
'store selection to a range variable Set rng = Selection
'loop through each area
For Each area In rng.Areas 'set a variable to the last cell in an area
Set tempR = area.Cells(area.Cells.Count) If lastCell Is Nothing Then
'initialize last cell the first time through Set lastCell = tempR
ElseIf tempR.Row > lastCell.Row Then 'if the row number is bigger, update last cell variable
Set lastCell = tempR
ElseIf tempR.Row = lastCell.Row Then
'if rows numbers are the same, update if the column is higher If tempR.Column > lastCell.Column Then
Set lastCell = tempR End If
End If Next
MsgBox lastCell.Address
9.6.10 Finding the Last Row and Column Numbers
The following code returns the row and column numbers of the last row with an entry and last column with an entry:
Dim
rNum
As LongrNum =
Cells.
Find(
What:="*",
After:=
Range("a1"), _
SearchOrder:=
xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox
"last row with an entry: " & rNum
DimcNum
As IntegercNum =
Cells.Find(What:="*",
After:=Range("a1"), _
SearchOrder:=xlByColumns, _SearchDirection:=xlPrevious).Column
MsgBox
"Last column with an entry: " & cNum
Once you have the above, you could then select the range from A1 to the bottom corner of the used range:
Range
(
Range("A1"),
Cells(rNum, cNum)).
SelectThe above approach is often preferred to using the UsedRange property of a sheet, as
UsedRange will sometimes return a much broader range due to formats beyond cells with entries.