Asked by:
Help with VBA for Excel - find row containing value / select row / delete row

Question
-
I'm another Excel newbie working on a school project using VBA for Excel. Currently I have a UserForm for users to enter their first name, last name, date and time. I have a table that records the data. Now what I would like to do is:
1. For each row in range of the sheet "Student Information", find the row that contains TextBoxFirstName.Value in column A AND TextBoxLastName.Value in column C AND TextBoxDate.Value in column G AND ListBoxTime.Value in column I.
2. Select column E in that row that fulfils the conditions above and save the value of that cell.
^ if value of that cell is "ADULT", convert that into "1" and save "1".
^ if value of that cell is "STUDENT", convert that into "2" and save "2".
^ if value of that cell is "SENIOR", convert that into "3" and save "3".
3. Delete that row.
4. Go to sheets("March") and find the row that contains TextBoxDate.Value in column C. Select that row.
5. Within 2 rows below the row, find the row that contains ListBoxTime.Value in column A. Select that row.
6. Within that row, find the first cell that contains the saved value - either 1, 2 or 3.
7. Select that cell and enter "." into that cell.
I am aware that it is a lengthy set of instructions, but I have experimented for 3 weeks without any progress, searched all online tutorials, but I couldn't figure it out.
Any help would be hugely appreciated !
Friday, February 15, 2013 10:21 AM
All replies
-
Do it in steps and post back when you get this first step working. I have assumed that your table on Student Information starts in Cell A1 and has a header row:
Sub Step1()
Dim myF As Range
Set myF = Worksheets("Student Information").Range("A1").CurrentRegion
myF.AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value
myF.AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value
myF.AutoFilter Field:=7, Criteria1:=TextBoxDate.Value
myF.AutoFilter Field:=9, Criteria1:=ListBoxTime.ValueEnd Sub
Since G and I are dates and times, you need to match the date and time formatting of your fields, which may mean using code like
myF.AutoFilter Field:=7, Criteria1:=Format(DateValue(TextBoxDate.Value),"mm/dd/yy")
myF.AutoFilter Field:=9, Criteria1:=Format(DateValue(ListBoxTime.Value),"hh:mm")Friday, February 15, 2013 2:19 PM -
Many thanks: this did find the row but - as the code says itself - applied an auto-filter. Is it possible to remove the auto-filter after the entire process?
But otherwise - the first step is successful.
- -
Apologies for the first paragraph - I've found a way to remove the auto-filter, so the first step is complete. Many thanks for your help! Troubled with the next steps - used Google and landed me with offset but the code did not work and debugging was merely useful to a newbie like me.
- Edited by ansonmiu14 Friday, February 15, 2013 3:03 PM
Friday, February 15, 2013 2:56 PM -
OK. First, you don't want to remove the autofilter, since that is how we identified the rows that have the data we want.
So, we add code to do the next steps:
Sub Macro1()
Dim myF As Range
Set myF = Worksheets("Student Information").Cells
myF.AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value
myF.AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value
myF.AutoFilter Field:=7, Criteria1:=TextBoxDate.Value
myF.AutoFilter Field:=9, Criteria1:=ListBoxTime.ValueDim myC As Range
Dim myD As Range
Dim myT As Range
Dim myN As Range
Dim myV As IntegerFor Each myC In Intersect(myF.Resize(myF.Rows.Count - 1).Offset(1), _
Worksheets("Student Information").Range("E:E")) _
.SpecialCells(xlCellTypeVisible)
myV = 0
If myC.Value = "ADULT" Then myV = 1
If myC.Value = "STUDENT" Then myV = 2
If myC.Value = "SENIOR" Then myV = 3
If myV <> 0 Then
Set myD = Worksheets("March").Range("C:C").Find(DateValue(TextBoxDate.Value))
If myD Is Nothing Then
MsgBox TextBoxDate.Value & " was not found on sheet ""March"""
Else
Set myT = myD.Offset(0, -2).Resize(3).Find(TimeValue(ListBoxTime.Value))
If myT Is Nothing Then
MsgBox ListBoxTime.Value & " was not found on sheet ""March"""
Else
Set myN = myT.EntireRow.Find(myV)
If myN Is Nothing Then
MsgBox myV & " was not found on ""March"" in " & myT.EntireRow.Address
Else
myN.Value = "."
MsgBox myN.Address & " was changed"
myC.EntireRow.Clear
End If
End If
End If
End If
Next myCmyF.AutoFilter
End Sub
- Edited by Bernie Deitrick, Excel MVP 2000-2010 Friday, February 15, 2013 4:19 PM
Friday, February 15, 2013 4:18 PM -
Thank you - entered the code and ran the macro, but nothing happened. I think I may have not articulated the instructions clearly; I'll try to re-word it.
1. For each row in the range of the sheet "Student Information", find the row that contains TextBoxFirstName.Value in column A AND TextBoxLastName.Value in column C AND TextBoxDate.Value in column G AND ListBoxTime.Value in column I.
2. Select the cell in column E in that row that fulfils the conditions above.
3. The cell will either have ADULT, STUDENT or SENIOR as its value.
4. If the value is "ADULT", convert that into "1" and save the value "1".
4. If the value of that cell is "STUDENT", convert that into "2" and save the value "2".
4. If the value of that cell is "SENIOR", convert that into "3" and save the value "3.
5. Delete that row found in step 1.
6. Go to sheets("March")
7. Find the row that contains TextBoxDate.Value in column C. (start finding from row 8).
8. Select that row.
9. Find the row that contains ListBoTime.Value in column A within 2 rows below the row found in step 7.
10. Select that row.
11. Find the first cell that contains the saved value - either 1, 2 or 3 - within the row selected in step 10.
12. Enter the value "." in that cell.
Saturday, February 16, 2013 2:08 AM -
That is what my code does, except for starting in row 8 for step 7.
Note that I put Message boxes into my code that communicate when things are done, but I didn't put any handling for no results being found on the filtering. So, change this line
For Each myC In Intersect(myF.Resize(myF.Rows.Count - 1).Offset(1), _
Worksheets("Student Information").Range("E:E")) _
.SpecialCells(xlCellTypeVisible)to this
For Each myC In Intersect(Intersect(myF.Resize(myF.Rows.Count - 1).Offset(1), _
Worksheets("Student Information").Range("E:E")), _
Worksheets("Student Information").UsedRange) _
.SpecialCells(xlCellTypeVisible)so that it will result in an error if no cells were found meeting your filtering criteria. Put a break on that line and see what is filtered at that point.
Saturday, February 16, 2013 1:23 PM -
I renamed the message box to error 1 and error 2 - your altered code functioned properly and selected the cell of STUDENT. However, the messagebox ERROR 1 came up - the code functioned until:
Set myD = Worksheets("March").Range("C:C").Find(DateValue(TextBoxDate.Value))
If myD Is Nothing Then
MsgBox TextBoxDate.Value & " was not found on sheet ""March"""
ElseIn this case, the date was 01 March 2013 in Sheets("March") with "3/1/2013" in the formula box. The date from the UserForm and the Sheets("Student Information") was "3/1/2013". Will the problem be the difference between the worded date and the numerical date?
Many thanks again.
- -
Edited the code and the first two message boxes did not pop up. But in the end, nothing happened and there were no messageboxes
- -
Somehow a debug again showed that your altered code from your post above had runtime error 1004 : no cells were found
- -
Fixed the code and went back to what I started with - first two messageboxes did not pop up so the values have been found. I am suspecting that the last section is not working:
Else myN.Value = "." MsgBox myN.Address & "was changed" myC.EntireRow.Clear
- Edited by ansonmiu14 Saturday, February 16, 2013 4:47 PM
Saturday, February 16, 2013 4:17 PM -
Finding dates can be a pain because formatting can be an issue.
Try changing
Set myD = Worksheets("March").Range("C:C").Find(DateValue(TextBoxDate.Value))
to
Set myD = Worksheets("March").Range("C:C").Find(DateValue(TextBoxDate.Value), LookAt:=xlValue)
If myD is Nothing Them Msgbox "Try different code!"
Saturday, February 16, 2013 5:31 PM -
Now the special cells has a compile error: invalid or unqualified reference highlighting the special cells in the For..... (xlCellTypeVisible)
I got it to work through adding "Stop" in the code but removing "stop" makes it not function again - why is this the case?
- Edited by ansonmiu14 Sunday, February 17, 2013 2:19 AM
Sunday, February 17, 2013 2:07 AM -
Private Sub CmdButtonUnbook_Click() If TextBoxFirstName.Value = "" Then MsgBox ("You must enter a first name!") Exit Sub ElseIf TextBoxLastName.Value = "" Then MsgBox ("You must enter a last name!") Exit Sub ElseIf ListBoxTime.Value = "" Then MsgBox ("You must select a time!") Exit Sub Else Dim myF As Range Set myF = Worksheets("Student Information").Cells myF.AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value myF.AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value myF.AutoFilter Field:=7, Criteria1:=TextBoxDate.Value myF.AutoFilter Field:=9, Criteria1:=ListBoxTime.Value Dim MyMonth As Integer Dim MonthName As String MyMonth = Month(TextBoxDate.Value) Select Case MyMonth Case 1 MonthName = "January" Case 2 MonthName = "February" Case 3 MonthName = "March" Case 4 MonthName = "April" Case 5 MonthName = "May" Case 6 MonthName = "June" Case 7 MonthName = "July" Case 8 MonthName = "August" Case 9 MonthName = "September" Case 10 MonthName = "October" Case 11 MonthName = "November" Case 12 MonthName = "December" End Select Dim myA As Range Dim myC As Range Dim myD As Range Dim myT As Range Dim myN As Range Dim myV As Integer 'For Each myC In Intersect(Intersect(myF.Resize(myF.Rows.Count - 1).Offset(1), _ ' Worksheets("Student Information").Range("E:E")), _ ' Worksheets("Student Information").UsedRange) ' myV = 0 ' If myC.Value = "Adult" Then myV = 1 ' If myC.Value = "Student" Then myV = 2 ' If myC.Value = "Senior" Then myV = 3 ' If myV <> 0 Then ' Set myD = Worksheets(MonthName).Range("C:L").Find(DateValue(TextBoxDate.Value)) ' If myD Is Nothing Then ' MsgBox TextBoxDate.Value & " was not fond on sheet " & MonthName ' Else ' Set myT = myD.Offset(1, -2).Find(ListBoxTime.Value) ' If myT Is Nothing Then ' MsgBox ListBoxTime.Value & " was not found on sheet " & MonthName ' Else ' Stop ' Stop ' Stop ' Resume ' ' Set myN = myT.EntireRow.Find(myV) ' If myN Is Nothing Then ' MsgBox myV & " was not found on " & MonthName & " in " & myT.EntireRow.Address ' Else ' myN.Value = "." ' MsgBox myN.Address & " was changed " ' myC.EntireRow.Delete (xlShiftUp) ' ' End If ' End If ' End If ' End If 'Next myC ' End If ' 'myF.AutoFilter Unload Me End Sub
I think it would be more helpful if I just show you the code because I am no coding expert to see where the problem lies - many thanks!
Ignoring the comment blocks, the only part where it doesn't work is the for statement. Otherwise, having the stops there and manually resuming them will eventually work for only the first entry in the student info table.
Sunday, February 17, 2013 2:54 PM -
Delete your textboxes, and insert a single listbox, named Listbox. Then try code like this, to force the selection of values and combinations of data that actually exist, by adding allowed values to the listbox and sequentially filtering as items are selected.
Option Explicit
Dim myF As Range
Dim shtSI As Worksheet
Dim varPossible As Variant
Dim varEach As Variant
Dim varList As Variant
Dim MyMonth As Integer
Dim MonthName As String
Dim strLastName As String
Dim strFirstName As String
Dim dteDate As Date
Dim dteTime As Date
Dim myA As Range
Dim myC As Range
Dim myD As Range
Dim myT As Range
Dim myN As Range
Dim myV As IntegerPrivate Sub CmdButtonUnbook_Click()
If Me.CmdButtonUnbook.Caption = "Choose Last Name" Then
strLastName = Me.ListBox.Value
myF.AutoFilter Field:=3, Criteria1:=strLastName
Me.CmdButtonUnbook.Caption = "Choose First Name"
MakeList 1
Exit Sub
End IfIf Me.CmdButtonUnbook.Caption = "Choose First Name" Then
strFirstName = Me.ListBox.Value
myF.AutoFilter Field:=1, Criteria1:=strFirstName
Me.CmdButtonUnbook.Caption = "Choose Date"
MakeList 7
Exit Sub
End IfIf Me.CmdButtonUnbook.Caption = "Choose Date" Then
dteDate = Me.ListBox.Value
myF.AutoFilter Field:=7, Criteria1:=Format(dteDate, myF.Cells(2, 7).NumberFormat)
MyMonth = Month(dteDate)
MonthName = Format(dteDate, "MMMM")
Me.CmdButtonUnbook.Caption = "Choose Time"
MakeList 9
Exit Sub
End IfIf Me.CmdButtonUnbook.Caption = "Choose Time" Then
dteTime = Me.ListBox.Value
myF.AutoFilter Field:=9, Criteria1:=Format(dteTime, myF.Cells(2, 9).NumberFormat)
Me.CmdButtonUnbook.Caption = "Proceed"
Exit Sub
End IfIf Me.CmdButtonUnbook.Caption = "Proceed" Then
For Each myC In myF.Columns(5).SpecialCells(xlCellTypeVisible)
myV = 0
If myC.Value = "Adult" Then myV = 1
If myC.Value = "Student" Then myV = 2
If myC.Value = "Senior" Then myV = 3
If myV <> 0 Then
Set myD = Worksheets(MonthName).Range("C:L").Find(DateValue(dteDate))
If myD Is Nothing Then
MsgBox dteDate & " was not found on sheet " & MonthName
Else
Set myT = myD.Offset(1, -2).Find(dteTime)
If myT Is Nothing Then
MsgBox dteTime & " was not found on sheet " & MonthName
Else
Set myN = myT.EntireRow.Find(myV)
If myN Is Nothing Then
MsgBox myV & " was not found on " & MonthName & " in " & myT.EntireRow.Address
Else
myN.Value = "."
MsgBox myN.Address & " was changed "
End If
End If
End If
End If
Next myCmyF.Columns(5).SpecialCells(xlCellTypeVisible).EntireRow.Delete
myF.AutoFilterEnd If
Unload Me
End Sub
Private Sub UserForm_Initialize()
Set shtSI = Worksheets("Student Information")
If shtSI.AutoFilterMode Then shtSI.ShowAllData
Set myF = shtSI.Range("A1", shtSI.Cells(shtSI.Rows.Count, 9).End(xlUp))
MakeList 3
Me.CmdButtonUnbook.Caption = "Choose Last Name"
End Sub
Private Sub MakeList(c As Integer)
Set varPossible = myF.Columns(c).Offset(1).Resize(myF.Rows.Count - 1).Cells
With CreateObject("Scripting.Dictionary")
For Each varEach In varPossible
If varEach.Value <> "" And (Not varEach.EntireRow.Hidden) And Not .exists(varEach.Value) Then
.Add IIf(c = 9, Format(varEach.Value, "h:mm"), varEach.Value), Nothing
End If
Next
varList = .keys
End With
With Me.ListBox
.Clear ' clear the listbox content
.List = varList
.ListIndex = 0 ' select the first item
End With
End Sub
- Edited by Bernie Deitrick, Excel MVP 2000-2010 Sunday, February 17, 2013 4:55 PM
Sunday, February 17, 2013 4:53 PM -
Thank you - is it possible not to use list boxes because listing every possible first name and every possible last name will be troublesome.
Basically I have a UserForm for booking which allows the user to input their details and their details will be transferred on the Sheets("Student Information"). My purpose of the UserForm for un-booking is to allow users to delete their information from the Sheets("Student Information") and to delete their "space" on the WorkSheet corresponding to the month of the date of their lesson. On the said worksheet, there is a row of 20 cells for each date and time (i.e 1/3/2013 10 AM - 12 PM) and the value "1" suggests an adult, value "2" suggests a student, value "3" suggests a senior and value "." suggests a vacant spot. I would like the UserForm for un-booking to find and delete the information in the Sheets("Student Information") and find the corresponding row of the date and time and delete the correct "space" according to their identity.
- -
I tried to substitute your previous FOR statement with "Set myC = myF.Columns(E).Cells" but there was an "application-defined or object-defined error". Is there a way to fix this? I think this is the main reason why the code is not working, as well as the unexplainable "mystery" of adding stops and manually resuming them to enable the code to work.
- Edited by ansonmiu14 Monday, February 18, 2013 3:23 PM
Monday, February 18, 2013 2:28 PM -
Try it this way - keep the last name text box, and add a listbox. Add a label that says "Enter you last name here" Then after the user enters their last name, everything else to be entered is done using available values in the listbox - I'm never sure how my name is entered (Bernard or Bernie), but I am sure of my last name - forget dates and times, too....
Option Explicit
Dim myF As Range
Dim shtSI As Worksheet
Dim varPossible As Variant
Dim varEach As Variant
Dim varList As Variant
Dim MyMonth As Integer
Dim MonthName As String
Dim strLastName As String
Dim strFirstName As String
Dim dteDate As Date
Dim dteTime As Date
Dim myA As Range
Dim myC As Range
Dim myD As Range
Dim myT As Range
Dim myN As Range
Dim myV As IntegerPrivate Sub CmdButtonUnbook_Click()
If Me.CmdButtonUnbook.Caption = "Choose Last Name" Then
strLastName = Me.TextBoxLastName.Value
Set myA = myF.Columns(3).Find(strLastName)
If myA Is Nothing Then
MsgBox "That last name was not found"
Exit Sub
End If
Me.TextBoxLastName.Enabled = False
Me.ListBox.Enabled = True
myF.AutoFilter Field:=3, Criteria1:=strLastName
Me.CmdButtonUnbook.Caption = "Choose First Name"
MakeList 1
Exit Sub
End IfIf Me.CmdButtonUnbook.Caption = "Choose First Name" Then
strFirstName = Me.ListBox.Value
myF.AutoFilter Field:=1, Criteria1:=strFirstName
Me.CmdButtonUnbook.Caption = "Choose Date"
MakeList 7
Exit Sub
End IfIf Me.CmdButtonUnbook.Caption = "Choose Date" Then
dteDate = Me.ListBox.Value
myF.AutoFilter Field:=7, Criteria1:=Format(dteDate, myF.Cells(2, 7).NumberFormat)
MyMonth = Month(dteDate)
MonthName = Format(dteDate, "MMMM")
Me.CmdButtonUnbook.Caption = "Choose Time"
MakeList 9
Exit Sub
End IfIf Me.CmdButtonUnbook.Caption = "Choose Time" Then
dteTime = Me.ListBox.Value
myF.AutoFilter Field:=9, Criteria1:=Format(dteTime, myF.Cells(2, 9).NumberFormat)
Me.CmdButtonUnbook.Caption = "Proceed"
Exit Sub
End IfIf Me.CmdButtonUnbook.Caption = "Proceed" Then
For Each myC In myF.Columns(5).SpecialCells(xlCellTypeVisible)
myV = 0
If myC.Value = "Adult" Then myV = 1
If myC.Value = "Student" Then myV = 2
If myC.Value = "Senior" Then myV = 3
If myV <> 0 Then
Set myD = Worksheets(MonthName).Range("C:L").Find(DateValue(dteDate))
If myD Is Nothing Then
MsgBox dteDate & " was not found on sheet " & MonthName
Else
Set myT = myD.Offset(1, -2).Find(dteTime)
If myT Is Nothing Then
MsgBox dteTime & " was not found on sheet " & MonthName
Else
Set myN = myT.EntireRow.Find(myV)
If myN Is Nothing Then
MsgBox myV & " was not found on " & MonthName & " in " & myT.EntireRow.Address
Else
myN.Value = "."
MsgBox myN.Address & " was changed "
End If
End If
End If
End If
Next myCmyF.Columns(5).SpecialCells(xlCellTypeVisible).EntireRow.Delete
myF.AutoFilter
myF.AutoFilterEnd If
Unload Me
End Sub
Private Sub UserForm_Initialize()
Set shtSI = Worksheets("Student Information")
If shtSI.AutoFilterMode Then shtSI.ShowAllData
Set myF = shtSI.Range("A1", shtSI.Cells(shtSI.Rows.Count, 9).End(xlUp))
Me.CmdButtonUnbook.Caption = "Choose Last Name"
Me.ListBox.Enabled = False
Me.TextBoxLastName.Enabled = True
End SubPrivate Sub MakeList(c As Integer)
Set varPossible = myF.Columns(c).Offset(1).Resize(myF.Rows.Count - 1).Cells
With CreateObject("Scripting.Dictionary")
For Each varEach In varPossible
If varEach.Value <> "" And (Not varEach.EntireRow.Hidden) And Not .exists(varEach.Value) Then
.Add IIf(c = 9, Format(varEach.Value, "h:mm"), varEach.Value), Nothing
End If
Next
varList = .keys
End With
With Me.ListBox
.Clear ' clear the listbox content
.List = varList
.ListIndex = 0 ' select the first item
End With
End SubMonday, February 18, 2013 3:38 PM -
Private Sub CmdButtonUnbook_Click() On Error Resume Next If TextBoxFirstName.Value = "" Then MsgBox ("You must enter a first name!") Exit Sub ElseIf TextBoxLastName.Value = "" Then MsgBox ("You must enter a last name!") Exit Sub ElseIf ComboBoxIdentity.Value = "" Then MsgBox ("You must select your identity!") Exit Sub ElseIf ListBoxTime.Value = "" Then MsgBox ("You must select a time!") Exit Sub Else Dim myF As Range Set myF = Worksheets("Student Information").Range("A3:Q100") myF.AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value myF.AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value myF.AutoFilter Field:=5, Criteria1:=ComboBoxIdentity.Value myF.AutoFilter Field:=7, Criteria1:=TextBoxDate.Value myF.AutoFilter Field:=9, Criteria1:=ListBoxTime.Value myF.EntireRow.Clear Dim MyMonth As Integer Dim MonthName As String MyMonth = Month(TextBoxDate.Value) Select Case MyMonth Case 1 MonthName = "January" Case 2 MonthName = "February" Case 3 MonthName = "March" Case 4 MonthName = "April" Case 5 MonthName = "May" Case 6 MonthName = "June" Case 7 MonthName = "July" Case 8 MonthName = "August" Case 9 MonthName = "September" Case 10 MonthName = "October" Case 11 MonthName = "November" Case 12 MonthName = "December" End Select Dim myD As Range Dim myT As Range Dim myN As Range Dim myV As Integer myV = 0 If ComboBoxIdentity.Value = "Adult" Then myV = 1 If ComboBoxIdentity.Value = "Student" Then myV = 2 If ComboBoxIdentity.Value = "Senior" Then myV = 3 If myV <> 0 Then Set myD = Worksheets(MonthName).Range("C:L").Find(TextBoxDate.Value) If myD Is Nothing Then MsgBox TextBoxDate.Value & " was not found on sheet " & MonthName Else If ListBoxTime.Value = "10 AM - 12 PM" Then Set myT = myD.Offset(1, -1) Set myN = myT.EntireRow.Find(myV).Replace(What:=myV, Replacement:=".") MsgBox "Your space has been unbooked." myF.AutoFilter Unload Me ElseIf ListBoxTime.Value = "5 PM - 7 PM" Then Set myT = myD.Offset(2, -1) MsgBox "Your space has been unbooked." myF.AutoFilter Unload Me End If End If End If End If End Sub
I finally have a working code! However the line of code "Set myN = myT.EntireRow.Find(myV)" does not work properly because it finds ALL the values. Is it possible to just find the FIRST occurrence of the value?Tuesday, February 19, 2013 1:54 PM -
Replace
Set myN = myT.EntireRow.Find(myV).Replace(What:=myV, Replacement:=".")
with the two lines
Set myN = myT.EntireRow.Find(myV, After:=myT.EntireRow.Cells(1, 1))
myN.Replace What:=myV, Replacement:="."Tuesday, February 19, 2013 2:12 PM -
Thank you so much for your continuous help.
My code essentially functions properly, but I am assuming that the AutoFilter does not work. If I have two separate entries such as:
Peter Robinson Adult 4/3/2013 10 AM - 12 PM peterobinson@gmail.com 555-555-5555
David Jones Adult 4/3/2013 10 AM - 12 PM davidjones@yahoo.com 555-555-5555
If I enter the details of David Jones in the UnBookForm, it deletes Peter Robinson's data from the sheet instead.
I was wondering whether there is an alternative to using AutoFilter, or whether the code can be modified to account for all the fields.
Friday, February 22, 2013 4:11 PM -
You are filtering on first name. Put a break on the last myF.Autofilter line (click on it and press F9) in this block of code:
Set myF = Worksheets("Student Information").Range("A3:Q100")
myF.AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value
myF.AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value
myF.AutoFilter Field:=5, Criteria1:=ComboBoxIdentity.Value
myF.AutoFilter Field:=7, Criteria1:=TextBoxDate.Value
myF.AutoFilter Field:=9, Criteria1:=ListBoxTime.Value
myF.EntireRow.Clearand remove this line:
myF.EntireRow.Clear
Then run the code and when you get to the break take a look at your StudentInformation Page. I suspect that you have much more data than 100 rows, so change "A3:Q100" to something larger, like "A3:Q10000"
Friday, February 22, 2013 4:25 PM -
With the break on the AutoFilter, it shows that the AutoFilter does not filter the name - it only shows the first entry of the identity. For instance, back to the previous example, if Peter Robinson is the first adult listed in the worksheet, it filters down to Peter Robinson regardless of what names I type in.Saturday, February 23, 2013 11:15 AM