In many of the examples you will see the Excel 97-2003 values of 65,536 rows and 256 columns hardcoded, as most users have not upgraded, and such references are easier to
'APPROACH 3 - THE SIMPLEST
9.3 ROW EXAMPLES
9.3.1 Determining The Currently Selected Cell's Row
The following returns the row number of the active cell and stores in a variable for later use R = ActiveCell.Row
9.3.2 Testing Whether A Row Is Selected
The following tests to see if a row is highlighted:
If Selection.Address = Selection.EntireRow.Address Then 'do something if highlighted
End If
9.3.3 How To Select All The Rows In A Database
Assume that your data starts in cell A1 of your worksheet and is bordered by blank rows, the following statement will select all the cells in the database:
Range("A1").CurrentRegion.Select
If you want to assign it to a range variable, then use a statement like the following:
Dim dataRange As Range
Set dataRange = Range("A1").CurrentRegion
If the data is not on the active worksheet, then qualify the Range reference with the sheet and if necessary the workbook:
Dim dataRange As Range Set dataRange = _
Workbooks("Book1.Xls").Sheets("Sheet1") _ .Range("A1").CurrentRegion
Or:
Dim dataRange As Range Dim wb As Workbook dim sh As Worksheet
Set wb = Workbooks("Book1.Xls") Set sh = wb.Sheets("Sheet1")
Set dataRange = sh.Range("A1").CurrentRegion
9.3.4 Selecting Rows Based On Cell Entries
By using AutoFilter, you can select just the rows that contain a certain entry. In this case, all rows with the value of 780 in column 1.
With Range("A1").CurrentRegion .AutoFilter 1, "=780"
.Offset(1).Resize(.Rows.Count-1) _ .SpecialCells(xlVisible).Select .AutoFilter
End With
The following macro will highlight all cells containing a string in all open workbooks.
Sub FindAll()
Dim strWhat As String Dim wb As Workbook Dim ws As Worksheet Dim r As Range Dim rFirst As Range
Dim bFirstStep As Boolean
strWhat = InputBox("Enter a string to find:") For Each wb In Application.Workbooks
For Each ws In wb.Sheets
Set rFirst = ws.Cells.Find(What:=strWhat, _ After:=ws.Cells(1, 1), _
LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False) If Not rFirst Is Nothing Then
HighLightIt rFirst
Set r = ws.Cells.FindNext(After:=rFirst) While (Not rFirst.Address = r.Address) HighLightIt r
Set r = ws.Cells.FindNext(After:=r) Wend
End If Next Next End Sub
Sub HighLightIt(r As Range)
r.Interior.ColorIndex = 6 End Sub
9.3.5 Select Odd-Numbered Rows
The following selects every odd row in the worksheet's used range:
Sub OddRows()
Dim rngRows As Range Dim i As Long
Set rngRows = Rows(1)
For i = 3 To Cells.SpecialCells(xlLastCell).Row If i Mod 2 = 1 Then
Set rngRows = Union(rngRows, Rows(i)) End If
Next i
Set rngRows = Intersect(rngRows, ActiveSheet.UsedRange) rngRows.Select
End Sub
9.3.6 How To Determine If A Selection Has Non-Contiguous Rows
The Range object has an Areas collection that allows you to access multiple non-contiguous ranges in a Selection. Therefore, if Selection.Areas.Count = 1 then you know the selected range is contiguous. If Selection.Areas.Count > 1 then you can access the different parts of the selection with
Dim rArea As Range
For Each rArea In Selection.Areas 'Do your stuff here
Next
Note that each rArea object iterated above is a Range object that can contain any number of cells/rows/columns, so you may need a second iteration inside the first one to do what you want if you want to work with the cells in each area.
9.3.7 Determining If A Row Or Column Is Empty
The following illustrates how to determine if a row is empty, in this case row 10.
If Application.CountA(Rows(10)) = 0 Then MsgBox "The row is empty"
End If
The following determine if column C is empty:
If Application.CountA(Columns(3)) = 0 Then MsgBox "The column is empty"
End If
If you need to determine if a the row containing the active cell is empty, then use the following approach:
If Application.CountA(ActiveCell.EntireRow) = 0 Then MsgBox "The row is empty"
End If
For a column check, you would use EntireColumn instead of EntireRow.
9.3.8 Duplicating The Last Row In A Set Of Data
The following code will duplicate the last row in a set of data. It assumes that there is always an entry in column A.
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row Rows(lastRow).Copy Cells(lastRow + 1, "A")
If you want to clear the entries in certain cells in this new last row, then you can use statements like the following:
Cells(lastRow + 1, "C").ClearContents
9.3.9 Inserting Multiple Rows
The following example looks for the word "test" (any case) in the range A1 to A50, and if found inserts two rows below the cell containing the word
Sub Insert_Row() Dim cell As Range
'rotate through all the cells in the range For Each cell In Range("A1:A50") 'check value and see if it equal to the word test
If LCase(cell.Value) = "test" Then 'if equal, insert two rows below the cell
cell.Offset(1, 0).EntireRow.Resize(2).Insert End If
Next End Sub
9.3.10 Insert Rows And Sum Formula When Cells Change
The following example checks a column of entries, ands and every time the entry in the column changes the macro inserts two rows and sums the numbers in another column. This macro also prompts the use to specify the first cell to be checked and for any cell in the column to be summed.
Sub Insert_Rows_And_Sum()
Dim cell As Range, sumCell As Range, comparisonValue Dim topSumRow As Integer, sumColumn As Integer
'get ranges
On Error Resume Next
Set cell = Application.InputBox( _
prompt:="Select the first cell in the ID column", _ Type:=8)
If cell Is Nothing Then Exit Sub Set sumCell = Application.InputBox(
prompt:="Select any cell in the column to be summed", _ Type:=8)
If sumCell Is Nothing Then Exit Sub 'turn off error handling
On Error GoTo 0 'initialize values
comparisonValue = cell.Value topSumRow = cell.Row
sumColumn = sumCell.Column 'loop until a blank cell is encountered
While Not IsEmpty(cell) 'check to see if value has changed
If cell.Value <> comparisonValue Then
'if the value has changed, insert two rows and a sum formula Range(cell.Offset(1, 0), _
cell.Offset(2, 0)).EntireRow.Insert
Cells(cell.Row + 1, sumColumn).Formula = _ "=Sum(" & Range(Cells(topSumRow, sumColumn), _
Cells(cell.Row, _
sumColumn)).Address(False, False) & ")"
'update the cell to be checked, the comparison value, and the 'top row number
Set cell = cell.Offset(3, 0) comparisonValue = cell.Value topSumRow = cell.Row
Else
'if the same value, set cell to the next cell Set cell = cell.Offset(1, 0) End If
Wend End Sub
9.3.11 An Example Of Inserting Rows And Sum Formulas
This macro goes down a column of cells, and whenever the value changes, it inserts two blank rows and then puts in a sum formula underneath the numbers in the column D.
Sub InsertTwoRowsAndSum() Dim oldVal
Dim rwTop As Long Dim colmn As Integer Dim offColmn As Integer
If IsEmpty(ActiveCell.Value) Then
MsgBox "You must select the top of the column " & _ Chr(13) & "you want to work on"
Exit Sub End If
'store the active cell's value and row number
'oldVal is used to compare to the active cell's value as it changes 'rwTop is used to refer to the first cell in the sum function oldVal = ActiveCell.Value
rwTop = ActiveCell.Row
'this is the column that will be summed colmn = 4
'determine the offset from the active cell's column offColmn = colmn - ActiveCell.Column
If offColmn = 0 Then
MsgBox "the column being checked for cell entry " & _ "changes can not be the same as the one being summed."
Exit Sub End If
'select the next cell
ActiveCell.Offset(1, 0).Select 'loop until an empty cell is encountered
While Not IsEmpty(ActiveCell)
'if the value of the cell is not the same as oldVal, insert a row 'and sum formulas
If ActiveCell.Value <> oldVal Then 'add row
ActiveCell.EntireRow.Insert xlUp 'add sum formulas
ActiveCell.Offset(0, offColmn).Formula = "=sum(" & _ Range(Cells(rwTop, colmn), _
Cells(ActiveCell.Row - 1, colmn)). _ Address(False, False) & ")"
'go down a row and insert another line ActiveCell.Offset(1, 0).Select ActiveCell.EntireRow.Insert xlUp
'store this row number for use in the sum formula and update oldVal rwTop = ActiveCell.Row + 1
oldVal = ActiveCell.Offset(1, 0).Value End If
'go to the next row and loop if not blank ActiveCell.Offset(1, 0).Select Wend
'handle the sum and row inserts needed for the last set of values ActiveCell.EntireRow.Insert xlUp
ActiveCell.Offset(0, offColmn).Formula = "=sum(" & _ Range(Cells(rwTop, colmn), _
Cells(ActiveCell.Row - 1, colmn)).Address(False, False) _
& ")"
ActiveCell.Offset(1, 0).Select ActiveCell.EntireRow.Insert xlUp End Sub
9.3.12 Deleting Rows
If you record a macro that deletes a row, you will get code like the following:
Rows("7:10").Select
Selection.Delete Shift:=xlUp
This gives you a clue to how to write the code, but some of the tricks are not obvious. The following examples show how to delete rows a number of different ways.
If you wish it to delete the row containing the active cell and the next two rows, you can do this way:
Dim A As Long, B As Long A = ActiveCell.Row
B = A + 2
Rows(A & ":" & B).Delete
The following is another approach that uses range variables instead.
Dim startCell As Range, endCell As Range Set startCell = ActiveCell
Set endCell = startCell.Offset(2, 0)
Range(startCell, endCell).EntireRow.Delete
If the startCell and endCell refer to cells on a sheet that is not the active sheet, then use this approach:
Dim oWS As Worksheet
Set oWS = startCell.Parent
oWS.Range(startCell, endCell).EntireRow.Delete This could also have been written:
startCell.Parent.Range(startCell, endCell).EntireRow.Delete
The key is qualifying the Range() method with the worksheet when the range being deleted is not on the active sheet.
9.3.13 Deleting Sets Of Rows
The following example deletes every 2nd and 3rd row (i.e. rows 2,3 and 5,6 and 8,9 and
>11,12, ...etc.) from a spreadsheet.
Sub DeleteEvery() Dim N As Integer Dim M As Integer With ActiveSheet
'calculate last row not to delete
M = Int((.UsedRange.Rows.Count + 1) / 3) * 3 + 1 'step in threes as two rows are deleted and one kept
For N = M To 4 Step -3
'delete the two rows above the row not to delete .Rows(N - 1).Delete
.Rows(N - 2).Delete Next N
End With End Sub
9.3.14 Deleting Error Rows
The problem facing the user is the following: In a worksheet there are three adjacent columns with the results of some calculations (about > 50 rows). The user wants to delete the rows where in column 1 AND 2 AND 3 the value is DIV/0!. If one of the three columns has a valid solution, then the user just wants to replace DIV/0! by a single quote and the row must not be deleted.
The following code accomplishes the above task. The user must first select a range of cells just in the first column and then run the macro.
Sub DeleteErrors()
Dim rng As Range, i As Long, j As Integer Dim eCount As Integer
'set a the range variable rng equal to the selection Set rng = Selection
'step through the selection rows backwards as rows are to be deleted For i = Selection.Rows.Count To 1 Step -1
eCount = 0
For j = 1 To 3
'loop through the three cells on each row. rng(i, 1) refers to the 'cell in the selected range, rng(i, 2) the cell in the column to the right, 'and rng(i,3) the cell two columns to the right. rng(i,2) and rng(i,3) are 'not in the selection or in the range variable rng.
If IsError(rng(i, j)) Then
'note that IsError does not distinguish what kind of error 'is in the cell
rng(i, j).Value = "-"
eCount = eCount + 1 End If
Next j
If eCount = 3 Then rng(i, 1).Resize(1, 3).Delete shift:=xlUp Next i
End Sub
9.3.15 Deleting Duplicate Rows
The following code will delete duplicate rows if the cell values in the rows in the selection are duplicates. The data first must be sorted, as this code compares each row to the next row.
Sub Delete_Duplicates() Dim iRows As Long Dim iCols As Long Dim RowMax As Long Dim ColMax As Long Dim bSame As Boolean Dim rowMin As Long Dim colMin As Long
'restrict range to check to just cells in the used range
With Intersect(Selection, ActiveSheet.UsedRange) 'make certain there are at least two rows
If .Rows.Count = 1 Then MsgBox "Pick more rows!"
Exit Sub End If
'make certain only one range is selected If .Areas.Count > 1 Then
MsgBox "Only a single area is allowed"
Exit Sub End If
'set min and max columns numbers rowMin = .Cells(1).Row + 1 RowMax = .Cells(.Cells.Count).Row colMin = .Cells(1).Column
ColMax = .Cells(.Cells.Count).Column End With
'check rows, starting from the bottom and working up For iRows = RowMax To rowMin Step -1
'initialize each time bSame = True 'check column values
For iCols = colMin To ColMax
If Cells(iRows, iCols).Value <> _ Cells(iRows - 1, iCols).Value Then 'if a difference is found set bSame to False
bSame = False Exit For
End If Next
'if bSame still true, delete the row
If bSame Then Rows(iRows).Delete Next
End Sub
9.3.16 Remove/Highlight Duplicate Rows
Assuming your data is in columns A, B, and C, you can do something like this:
1)select the data
2) use macro code like the following. Note that it deletes from the bottom up.
Dim I As Integer
For I = Selection.Cells(Selection.Cells.Count).Row To _ Selection.Cells(1).Row + 1 Step -1
If Cells(I, 1).Value = Cells(I - 1, 1).Value And _ Cells(I, 2) = Cells(I - 1, 2).Value And _
Cells(I, 3).Value = Cells(I - 1, 3).Value Then _ Rows(I).EntireRow.Delete
Next I
9.3.17 Conditionally Deleting Rows
When you are writing code to delete entire rows, you should always run your loops backwards, from the bottom of the range to the top. This prevents Excel from skipping Rows. The following illustrates a way to do this. In this example and the subsequent examples, range D8 to D15 is checked and if the value in the cell is zero, then the row is deleted.
Dim RowNdx As Long
For RowNdx = 15 To 8 Step -1
If Cells(RowNdx, "D").Value = 0 Then Cells(RowNdx, "D").EntireRow.Delete
End If Next RowNdx
Another way to have the job done would be a routine along these lines, which utilizes the ability of Excel to delete non-contiguous rows.
Dim DelRange As Range Dim c As Range
For Each c In ActiveSheet.Range("D8:D15").Cells If c.Value = 0 Then
If DelRange Is Nothing Then Set DelRange = c.EntireRow Else
Set DelRange = Union(DelRange, c.EntireRow) End If
End If Next c
'turn on error handling in case no range is assigned On Error Resume Next
DelRange.Delete On Error GoTo 0
It takes up a couple of lines more , but is about 5 times as fast. 10000 lines in about 10 sec.
instead of about 54 sec.(On a Pentium 200 Mhz)
The following is another way to select specify rows and delete them without looping. It makes use of the SpecialCells method.
'turn On Error handling in case no matching cells On Error Resume Next
Range("D7:D15").AutoFilter Field:=1, Criteria1:="0"
If Err = 0 Then _
Range("D8:D15").SpecialCells(xlCellTypeVisible) _ .EntireRow.Delete
ActiveSheet.AutoFilterMode = False ActiveSheet.UsedRange
'turn off error handling On Error GoTo 0
Another technique that is similar is again to use SpecialCells, but this time set a Boolean value in the rows to be selected:
For Each c In Range("D8:D15").Cells If c.Value = 0 Then c = True
Next c
'Delete all in 1 shot, turn on error handling in case no matching rows On Error Resume Next
Range("D8:D15").SpecialCells(xlCellTypeConstants, _ xlLogical).EntireRow.Delete
'Turn off error handling On Error GoTo 0
9.3.18 How To Delete Blank Rows
You can use the following statements to find the blank cells in a column, and then delete the rows:
On Error Resume Next
Intersect(Activesheet.UsedRange,Columns("A:A")). _ SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0
The On Error statements are needed in case there are no blank cells in the column and in the used range.
The following is an alternate way that checks each row and deletes the only only if there are no entries in the row: