Sure... Here is a probably long-winded explanation for each line:
Option Explicit ' This says that each variable must be EXPLICITly defined.
' Which means that there must be a Dim,Public,Global, etc
' Statement for each variable used.
' It has saved me literally HUNDREDS of hours in debugging
' time. Missing or transposed letters in variable names
' can be very hard to find without it.
Sub Updates2() ' Self explanatory
'
Dim Data_Rowcnt, I, C ' Declaration of Local variables
Dim HideCnt, AlertFlag ' Remember, variables declared within a Sub/Function
' Are only available to THAT Sub or Function.
' That enables you to use a loop counter like "I"
' in one Sub, and if within this sub you call another
' Sub or Function, you can still use "I" there
' without losing the original value.
HideCnt = 0 ' The original script HID the records rather than deleting them.
' For Status reporting, I like to keep track of the number of
records
' Being affected.
Application.ScreenUpdating = False 'If you like seeing your spreadsheet
flash and
'dance while your script runs, just so
that you
'know it's still doing something, then by
all
'means, comment out this line, but all
the
'screen updating eats up CPU cycles
(makes the
'program run longer). I like it to be
fast
'without all the pretty lights.
Data_Rowcnt = Application.WorksheetFunction.CountA(Range("A1: A65500"))
' Most (if not all) "regular" Excel worksheet functions are
available to
' VBA. In Excel, if I wanted to know the number of non-blank cells
in a
' column, I would use =COUNTA(A:A). Instead of putting it in a
cell,
' I used the vba equivalent... ("Application" is Excel)
Cells.EntireRow.Hidden = False 'This "unhides" all rows... Not really
necessary
'Any longer, since we changed the Sub to
delete
'the rows instead of hiding them.
Application.StatusBar = "Processing " & Data_Rowcnt & " Records"
'This puts the Status bar to work for you. Instead of it saying things
like:
'"Ready", or "please wait..", I like to have it show what the macro/sub is
'actually DOING. Here, it gives the number or records about to be
processed.
For I = Data_Rowcnt To 1 Step -1 'Since we're deleting rows, the "row count"
is
'constantly changing. Let's say that you have 100 rows. When you get
to
'row 10, the test indicates that you want to delete the row.
'Once deleted, row 11 moves to row 10.
'In the next iteration of the loop, row 11 is tested, but that means
that
'the current row 10 is skipped! Therefore, I START the loop at the end
'of the list (Data_Rowcnt) and work backwards to row 1, one row at a
time
'(step -1).
If (I Mod (Round(Data_Rowcnt / 20, 0)) = 0) Then _
Application.StatusBar = I & " of " & Data_Rowcnt & " = " _
& Round((I / Data_Rowcnt) * 100, 0) & "%"
'As I mentioned before, I like to see what's going on by looking
'at the Status bar. My goal here is to see something like:
' 561 of 11220 = 5%
'Now, if you're processing a LOT of records, displaying updates
'for every record uses a LOT of CPU time, but only showing the
'status every 10% may be too long to provide adequate feedback.
'So, in this case I wanted to show the status every 5%.
'Now, how many records are 5% of the total?
'That would be the total records / 20. So, when the loop counter
'reaches a multiple of the 5% number, you want to update the
'Status bar. But, let's say the record count is 11,229, then the
'5% count is 561.45. The loop counter will never match this
because
'it is a whole number. So, I rounded it to "0" places.
' Round(Data_Rowcnt / 20, 0)
' Next is the "mod" function. Mod gives you the REMAINDER of a
'division operation. For instance, 10/2 is 5, with a remainder of
0.
'so, 5 mod 10 = 0. 10/3 = 3, with a remainer of 1, so 3 mod 10 =
0.
'Now, using the 5% number, I check to see if the loop counter
divided
'by the 5% number has a 0 remainder.. which means it has completed
'the NEXT 5%..
' I then have to come up with a message that is meaningful.
' For that, I calculate the % complete I/Data_Rowcnt * 100.
' Seems like a lot of work for just a display, but I have to make
'it look like I'm doing SOMETHING!
AlertFlag = False 'Used to indicate if a line should be deleted
For C = 1 To 26 'Cycle through columns A to Z
If (UCase(Cells(I, C)) = "F") Then 'Check for a value of "F"
Cells(I, C) = "Awaiting" 'Change the "F" to "Awaiting"
AlertFlag = True
End If
Next C
' I think this next statement needs some work.
' It seems to be missing some "associations"
'It looks like you want to delete a line if
'There was no "F", and "passport" is in column 12 or 13.
' If (UCase(Cells(I, 12)) = "PASSPORT") And (Not AlertFlag) _
' Or (UCase(Cells(I, 13)) = "PASSPORT") And (Not AlertFlag) Then
' I would change it to:
If ((Not AlertFlag) _
And ((UCase(Cells(I, 12)) = "PASSPORT") _
Or (UCase(Cells(I, 13)) = "PASSPORT"))) Then
HideCnt = HideCnt + 1 'Increment record counter
Rows(I).Delete Shift:=xlUp 'Delete the row
End If
Next I 'Proceed to next line
Cells.Select 'Select all remaining cells
'I think this sort needs some work.
'It looks like it was recorded from a result set with only one record.
'then modified. I don't think there should be an Order2 without a Key2.
'You might want to re-record this with a larger data set, perhaps even
'all of the records so that the range will be much larger than anticipated.
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select 'Set cursor to a known position (un-select any
selected cells)
Application.ScreenUpdating = True 'Once complete, display all of the
changes you've made.
'The Status bar will still display the last updated message until you exit
excel.
'since you probably want to see other message, release the status bar so
Excel can use it.
Application.StatusBar = False
End Sub
We aren't doing anything with the HideCnt variable.
Before exiting, you could display the number of records using something like:
Msgbox HideCnt & " Records to Correct"