Excel: Hide a row in Excel 2003/XP/2000/97

Also learn how to hide a row in Excel 2007

Question: How do I hide a row from being displayed in Excel 2003/XP/2000/97?

Answer: Select the entire row that you wish to hide.
Right-click and select "Hide" from the popup menu.Your row should now be hidden.

Hide a row in Excel 2007

Also Learn how to Hide a row in Excel 2003/XP/2000/97

Question: How do I hide a row from being displayed in Excel 2007?

Answer: Select the entire row that you wish to hide.

Right-click and select "Hide" from the popup menu.Your row should now be hidden.

Excel Topics: Rows

Hide / UnHide
Hide a Row in Excel 2007/Excel 2003/XP/2000/97
UnHide a row in Excel 2007/ Excel 2003/XP/2000/97
UnHide row 1 in Excel 2007/Excle 2003/XP/2000/97

Insert / Delete:
Insert a New Row in Excel 2007/ Excel 2003/XP/2000/97
Delete a Row in Excel 2007/Excle 2003/XP/2000/97

Sizing :
Change the height of a row in Excel 2007/ Excel 2003/XP/2000/97
Stop Wrap Text when pasting in Excel 2007/Excle 2003/XP/2000/97

Condition Formatting:
Automatically alrernate Row colors (One shaded, one white) in Excel 2007/Excle 2003/XP/2000/97
Automatically alternate row colors (Two shaded, tow white) in Excel 2007/ Excel 2003/XP/2000/97
Automatically alternate row colors (three shaded, three white ) in Excel 2007/Excle 2003/XP/2000/97

Create a column that must contain unique values in Excel 2003/XP/2000/97

Question: I'm looking for tips which can help me in creating a data column (Alphanumeric) in Excel 2003/XP/2000/97 where each row has to hold unique data. If a user enters a duplicate value in the column, the spreadsheet should not accept this value.

Answer: There are several "events" available within an Excel spreadsheet where you can place VBA code. In your case, we want to check for a duplicate value when the "Worksheet_Change" event fires.

Let's take a look at an example.
In our spreadsheet, we've set up column A in Sheet1 to contain unique values. On this sheet, we've placed code on the "Worksheet_Change" event, so that whenever a value is entered in column A (within the first 200 rows), the macro will test to see if this value has been entered before.

If the value is a duplicate, the following message will appear:

And the background color of the cell that contains the duplication will turn red.

In this example, we've entered the value 1234 in cell A5 which already exists in cell A2.

You can press Alt-F11 to view the VBA code.



Macro Code:
The macro code looks like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim LLoop As Integer
Dim LTestLoop As Integer

Dim Lrows As Integer
Dim LRange As String
Dim LChangedValue As String
Dim LTestValue As String

'Test first 200 rows in spreadsheet for uniqueness
Lrows = 200
LLoop = 2

'Check first 200 rows in spreadsheet
While LLoop <= Lrows
LChangedValue = "A" & CStr(LLoop)

If Not Intersect(Range(LChangedValue), Target) Is Nothing Then
If Len(Range(LChangedValue).Value) > 0 Then

'Test each value for uniqueness
LTestLoop = 2
While LTestLoop <= Lrows
If LLoop <> LTestLoop Then
LTestValue = "A" & CStr(LTestLoop)
'Value has been duplicated in another cell
If Range(LChangedValue).Value = Range(LTestValue).Value Then
'Set the background color to red
Range(LChangedValue).Interior.ColorIndex = 3
MsgBox Range(LChangedValue).Value & " already exists in cell A" & LTestLoop
Exit Sub
Else
Range(LChangedValue).Interior.ColorIndex = xlNone
End If

End If

LTestLoop = LTestLoop + 1
Wend

End If
End If

LLoop = LLoop + 1
Wend

End Sub

Test for duplicates in eight columns, combined (and clear the values in the 8 columns when a duplicate is found) in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, is it possible to write a macro to check 8 columns over 2000 rows and clear the values in the 8 columns when a duplicate is found?

Answer: Let's take a look at an example.

In our spreadsheet, we've set up values in columns A through I. On Sheet1, we've created a button that when clicked will launch a macro. This macro will clear the values in columns A through H when a duplicate is found (based on the values in columns A through H). It does not clear the value in column I when a duplicate is found.

When the macro has completed, a message box will appear that indicates how many duplicates were cleared..

After the macro has run, you can see that cells A through H of the duplicate have been cleared, as seen below in rows 9 and 16.

You can press Alt-F11 to view the VBA code.

Please note that the LRows variable in this macro is set to 2000 indicating that the macro will test the first 2000 rows in for duplicates. You may need to change this value to accommodate your volume of data.



Macro Code:
The macro code looks like this:

Sub TestForDups()

Dim LLoop As Integer
Dim LTestLoop As Integer

Dim Lrows As Integer
Dim LRange As String

Dim LCnt As Integer

'Column values
Dim LColA_1, LColB_1, LColC_1, LColD_1, LColE_1, LColF_1, LColG_1, LColH_1 As String
Dim LColA_2, LColB_2, LColC_2, LColD_2, LColE_2, LColF_2, LColG_2, LColH_2 As String

'Test first 2000 rows in spreadsheet for duplicates (clear any duplicates found)
Lrows = 2000
LLoop = 2
LCnt = 0

'Check first 2000 rows in spreadsheet
While LLoop <= Lrows
LColA_1 = "A" & CStr(LLoop)
LColB_1 = "B" & CStr(LLoop)
LColC_1 = "C" & CStr(LLoop)
LColD_1 = "D" & CStr(LLoop)
LColE_1 = "E" & CStr(LLoop)
LColF_1 = "F" & CStr(LLoop)
LColG_1 = "G" & CStr(LLoop)
LColH_1 = "H" & CStr(LLoop)

If Len(Range(LColA_1).Value) > 0 Then

'Test each value for uniqueness
LTestLoop = LLoop + 1
While LTestLoop <= Lrows
If LLoop <> LTestLoop Then
LColA_2 = "A" & CStr(LTestLoop)
LColB_2 = "B" & CStr(LTestLoop)
LColC_2 = "C" & CStr(LTestLoop)
LColD_2 = "D" & CStr(LTestLoop)
LColE_2 = "E" & CStr(LTestLoop)
LColF_2 = "F" & CStr(LTestLoop)
LColG_2 = "G" & CStr(LTestLoop)
LColH_2 = "H" & CStr(LTestLoop)

'Value has been duplicated in another cell (based on values in columns A to H)
If (Range(LColA_1).Value = Range(LColA_2).Value) _
And (Range(LColB_1).Value = Range(LColB_2).Value) _
And (Range(LColC_1).Value = Range(LColC_2).Value) _
And (Range(LColD_1).Value = Range(LColD_2).Value) _
And (Range(LColE_1).Value = Range(LColE_2).Value) _
And (Range(LColF_1).Value = Range(LColF_2).Value) _
And (Range(LColG_1).Value = Range(LColG_2).Value) _
And (Range(LColH_1).Value = Range(LColH_2).Value) Then

'Clear the duplicate
Range("A" & CStr(LTestLoop) & ":H" & CStr(LTestLoop)).Select
Selection.ClearContents

LCnt = LCnt + 1

End If

End If

LTestLoop = LTestLoop + 1
Wend

End If

LLoop = LLoop + 1
Wend

'Reposition back on cell A1
Range("A1").Select
MsgBox CStr(LCnt) & " rows have been cleared."

End Sub

Test for duplicates in eight columns, combined (and delete duplicates and originals that were duplicated) in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, is it possible to write a macro to check 8 columns over 2000 rows and delete the duplicates as well as the original row that the duplicate was based on?

Answer: Let's take a look at an example.
In our spreadsheet, we've set up values in columns A through H. On Sheet1, we've created a button that when clicked will launch a macro. This macro will delete any duplicate values as well as the original row that the duplicate was based on (based on the values in columns A through H).

When the macro has completed, a message box will appear that indicates how many duplicate rows were deleted.

After the macro has run, you can see that four rows have been deleted.

You can press Alt-F11 to view the VBA code.

Please note that the LRows variable in this macro is set to 2000 indicating that the macro will test the first 2000 rows in for duplicates. You may need to change this value to accommodate your volume of data.



Macro Code:
The macro code looks like this:

Sub TestForDups()

Dim LLoop As Integer
Dim LTestLoop As Integer

Dim Lrows As Integer
Dim LRange As String

Dim LCnt As Integer

'Column values
Dim LColA_1, LColB_1, LColC_1, LColD_1, LColE_1, LColF_1, LColG_1, LColH_1, LColI_1 As String
Dim LColA_2, LColB_2, LColC_2, LColD_2, LColE_2, LColF_2, LColG_2, LColH_2, LColI_2 As String

'Test first 2000 rows in spreadsheet for duplicates (delete any duplicates found as well
' as the original row)
Lrows = 2000
LLoop = 2

'First pass: Check first 2000 rows in spreadsheet (only flag records for deletion)
While LLoop <= Lrows
LColA_1 = "A" & CStr(LLoop)
LColB_1 = "B" & CStr(LLoop)
LColC_1 = "C" & CStr(LLoop)
LColD_1 = "D" & CStr(LLoop)
LColE_1 = "E" & CStr(LLoop)
LColF_1 = "F" & CStr(LLoop)
LColG_1 = "G" & CStr(LLoop)
LColH_1 = "H" & CStr(LLoop)
LColI_1 = "I" & CStr(LLoop)

If Len(Range(LColA_1).Value) > 0 Then

'Test each value for uniqueness
LTestLoop = LLoop + 1
While LTestLoop <= Lrows
If LLoop <> LTestLoop Then
LColA_2 = "A" & CStr(LTestLoop)
LColB_2 = "B" & CStr(LTestLoop)
LColC_2 = "C" & CStr(LTestLoop)
LColD_2 = "D" & CStr(LTestLoop)
LColE_2 = "E" & CStr(LTestLoop)
LColF_2 = "F" & CStr(LTestLoop)
LColG_2 = "G" & CStr(LTestLoop)
LColH_2 = "H" & CStr(LTestLoop)
LColI_2 = "I" & CStr(LTestLoop)

'Value has been duplicated in another cell (based on values in columns A to H)
If (Range(LColA_1).Value = Range(LColA_2).Value) _
And (Range(LColB_1).Value = Range(LColB_2).Value) _
And (Range(LColC_1).Value = Range(LColC_2).Value) _
And (Range(LColD_1).Value = Range(LColD_2).Value) _
And (Range(LColE_1).Value = Range(LColE_2).Value) _
And (Range(LColF_1).Value = Range(LColF_2).Value) _
And (Range(LColG_1).Value = Range(LColG_2).Value) _
And (Range(LColH_1).Value = Range(LColH_2).Value) Then

'Flag the duplicate and original for deletion
Range(LColI_1).Value = "DELETE"
Range(LColI_2).Value = "DELETE"

End If
End If

LTestLoop = LTestLoop + 1
Wend

End If

LLoop = LLoop + 1
Wend

LCnt = 0
LLoop = 2

'Second pass: Delete records flagged for deletion
While LLoop <= Lrows
If Range("I" & CStr(LLoop)) = "DELETE" Then

'Delete row
Rows(CStr(LLoop) & ":" & CStr(LLoop)).Select
Selection.Delete Shift:=xlUp

'Decrement counter since row was deleted
LLoop = LLoop - 1

LCnt = LCnt + 1

End If

LLoop = LLoop + 1
Wend

'Reposition back on cell A1
Range("A1").Select
MsgBox CStr(LCnt) & " rows have been deleted."

End Sub

Test for duplicates in eight columns, combined (and delete duplicates) in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, is it possible to write a macro to check 8 columns over 2000 rows and delete the duplicates?

Answer: Let's take a look at an example.
In our spreadsheet, we've set up values in columns A through H. On Sheet1, we've created a button that when clicked will launch a macro. This macro will delete any duplicate values (based on the values in columns A through H).

When the macro has completed, a message box will appear that indicates how many duplicate rows were deleted.

After the macro has run, you can see that two rows have been deleted.

You can press Alt-F11 to view the VBA code.

Please note that the LRows variable in this macro is set to 2000 indicating that the macro will test the first 2000 rows in for duplicates. You may need to change this value to accommodate your volume of data.



Macro Code:
The macro code looks like this:

Sub TestForDups()

Dim LLoop As Integer
Dim LTestLoop As Integer

Dim Lrows As Integer
Dim LRange As String

Dim LCnt As Integer

'Column values
Dim LColA_1, LColB_1, LColC_1, LColD_1, LColE_1, LColF_1, LColG_1, LColH_1 As String
Dim LColA_2, LColB_2, LColC_2, LColD_2, LColE_2, LColF_2, LColG_2, LColH_2 As String

'Test first 2000 rows in spreadsheet for duplicates (delete any duplicates found)
Lrows = 2000
LLoop = 2
LCnt = 0

'Check first 2000 rows in spreadsheet
While LLoop <= Lrows
LColA_1 = "A" & CStr(LLoop)
LColB_1 = "B" & CStr(LLoop)
LColC_1 = "C" & CStr(LLoop)
LColD_1 = "D" & CStr(LLoop)
LColE_1 = "E" & CStr(LLoop)
LColF_1 = "F" & CStr(LLoop)
LColG_1 = "G" & CStr(LLoop)
LColH_1 = "H" & CStr(LLoop)

If Len(Range(LColA_1).Value) > 0 Then

'Test each value for uniqueness
LTestLoop = LLoop + 1
While LTestLoop <= Lrows
If LLoop <> LTestLoop Then
LColA_2 = "A" & CStr(LTestLoop)
LColB_2 = "B" & CStr(LTestLoop)
LColC_2 = "C" & CStr(LTestLoop)
LColD_2 = "D" & CStr(LTestLoop)
LColE_2 = "E" & CStr(LTestLoop)
LColF_2 = "F" & CStr(LTestLoop)
LColG_2 = "G" & CStr(LTestLoop)
LColH_2 = "H" & CStr(LTestLoop)

'Value has been duplicated in another cell (based on values in columns A to H)
If (Range(LColA_1).Value = Range(LColA_2).Value) _
And (Range(LColB_1).Value = Range(LColB_2).Value) _
And (Range(LColC_1).Value = Range(LColC_2).Value) _
And (Range(LColD_1).Value = Range(LColD_2).Value) _
And (Range(LColE_1).Value = Range(LColE_2).Value) _
And (Range(LColF_1).Value = Range(LColF_2).Value) _
And (Range(LColG_1).Value = Range(LColG_2).Value) _
And (Range(LColH_1).Value = Range(LColH_2).Value) Then

'Delete the duplicate
Rows(CStr(LTestLoop) & ":" & CStr(LTestLoop)).Select
Selection.Delete Shift:=xlUp

'Decrement counter since row was deleted
LTestLoop = LTestLoop - 1

LCnt = LCnt + 1

End If

End If

LTestLoop = LTestLoop + 1
Wend

End If

LLoop = LLoop + 1
Wend

'Reposition back on cell A1
Range("A1").Select
MsgBox CStr(LCnt) & " rows have been deleted."

End Sub

Test for duplicates in two columns, combined in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, is it possible to write a macro which would highlight any duplicate values where both columns A and B in two or more lines are the same?

Answer: Let's take a look at an example.
In our spreadsheet, we've set up values in both columns A and B. On this sheet, we've created a button that when clicked will launch a macro. This macro will highlight any duplicate values where both columns A and B in two or more lines are the same.

In our example, we've clicked on the button. Now the background color of the duplicates will turn red as follows:

In this example, the same values have been entered in rows 2 and 6.

You can press Alt-F11 to view the VBA code.

Please note that the LRows variable in this macro is set to 200 indicating that the macro will test the first 200 rows in columns A and B for duplicates. You may need to change this value to accommodate your volume of data.



Macro Code:
The macro code looks like this:

Sub TestForDups()

Dim LLoop As Integer
Dim LTestLoop As Integer
Dim LClearRange As String

Dim Lrows As Integer
Dim LRange As String

'Column A values
Dim LChangedValue As String
Dim LTestValue As String

'Column B values
Dim LChangedValueB As String
Dim LTestValueB As String

'Test first 200 rows in spreadsheet for uniqueness
Lrows = 200
LLoop = 2

'Clear all flags
LClearRange = "A2:B" & Lrows
Range(LClearRange).Interior.ColorIndex = xlNone

'Check first 200 rows in spreadsheet
While LLoop <= Lrows
LChangedValue = "A" & CStr(LLoop)
LChangedValueB = "B" & CStr(LLoop)

If Len(Range(LChangedValue).Value) > 0 Then

'Test each value for uniqueness
LTestLoop = 2
While LTestLoop <= Lrows
If LLoop <> LTestLoop Then
LTestValue = "A" & CStr(LTestLoop)
LTestValueB = "B" & CStr(LTestLoop)
'Value has been duplicated in another cell
If (Range(LChangedValue).Value = Range(LTestValue).Value) And (Range(LChangedValueB).Value = Range(LTestValueB).Value) Then
'Set the background color to red in column A
Range(LChangedValue).Interior.ColorIndex = 3
Range(LTestValue).Interior.ColorIndex = 3

'Set the background color to red in column B
Range(LChangedValueB).Interior.ColorIndex = 3
Range(LTestValueB).Interior.ColorIndex = 3

End If

End If

LTestLoop = LTestLoop + 1
Wend

End If

LLoop = LLoop + 1
Wend

End Sub

Test for duplicates on partial cell contents in a column in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, is it possible to write a macro which would highlight any duplicate values in column A? My problem is that sometimes the cell values do not match exactly.

For example, cell A1 contains "1234" and cell A2 contains "1234, 5678". Is there a way to compare partial cell contents so that a duplicate entry flag would be set for cells A1 and A2 in this example?

Answer: Let's take a look at an example.

In our spreadsheet, we've set up column A to contain unique values. On this sheet, we've created a button that when clicked will launch a macro. This macro will highlight any duplicate values in column A.

In our example, we've clicked on the button. Now the background color of the partial duplicates will turn red as follows:

In this example, the partial value of 1234 has been entered in cells A2, A5, and A6.

You can press Alt-F11 to view the VBA code.

Please note that the LRows variable in this macro is set to 200 indicating that the macro will test the first 200 rows in column A for duplicates. You may need to change this value to accommodate your volume of data.



Macro Code:
The macro code looks like this:

Sub TestForDups()

Dim LLoop As Integer
Dim LTestLoop As Integer
Dim LClearRange As String

Dim Lrows As Integer
Dim LRange As String
Dim LChangedValue As String
Dim LTestValue As String

'Test first 200 rows in spreadsheet for uniqueness
Lrows = 200
LLoop = 2

'Clear all flags
LClearRange = "A2:A" & Lrows
Range(LClearRange).Interior.ColorIndex = xlNone

'Check first 200 rows in spreadsheet
While LLoop <= Lrows
LChangedValue = "A" & CStr(LLoop)

If Len(Range(LChangedValue).Value) > 0 Then

'Test each value for uniqueness
LTestLoop = 2
While LTestLoop <= Lrows
If LLoop <> LTestLoop Then
LTestValue = "A" & CStr(LTestLoop)
'Value has been duplicated in another cell
If InStr(Range(LTestValue).Value, Range(LChangedValue).Value) > 0 Then
'Set the background color to red
Range(LChangedValue).Interior.ColorIndex = 3
Range(LTestValue).Interior.ColorIndex = 3

End If

End If

LTestLoop = LTestLoop + 1

Wend

End If

LLoop = LLoop + 1
Wend

End Sub

Test for duplicates in a column in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, is it possible to write a macro which would highlight any duplicate values in column A?
Answer: Let's take a look at an example.

In our spreadsheet, we've set up column A to contain unique values. On this sheet, we've created a button that when clicked will launch a macro. This macro will highlight any duplicate values in column A.

In our example, we've clicked on the button. Now the background color of the duplicates will turn red as follows:

In this example, the same value has been entered in both cells A2 and A5.

You can press Alt-F11 to view the VBA code.

Please note that the LRows variable in this macro is set to 200 indicating that the macro will test the first 200 rows in column A for duplicates. You may need to change this value to accommodate your volume of data.

Macro Code:
The macro code looks like this:

Sub TestForDups()

Dim LLoop As Integer
Dim LTestLoop As Integer
Dim LClearRange As String

Dim Lrows As Integer
Dim LRange As String
Dim LChangedValue As String
Dim LTestValue As String

'Test first 200 rows in spreadsheet for uniqueness
Lrows = 200
LLoop = 2

'Clear all flags
LClearRange = "A2:A" & Lrows
Range(LClearRange).Interior.ColorIndex = xlNone

'Check first 200 rows in spreadsheet
While LLoop <= Lrows LChangedValue = "A" & CStr(LLoop) If Len(Range(LChangedValue).Value) > 0 Then

'Test each value for uniqueness
LTestLoop = 2
While LTestLoop <= Lrows If LLoop <> LTestLoop Then
LTestValue = "A" & CStr(LTestLoop)
'Value has been duplicated in another cell
If Range(LChangedValue).Value = Range(LTestValue).Value Then
'Set the background color to red
Range(LChangedValue).Interior.ColorIndex = 3
Range(LTestValue).Interior.ColorIndex = 3

End If

End If

LTestLoop = LTestLoop + 1

Wend

End If

LLoop = LLoop + 1
Wend

End Sub