I am a VBA newbie and need some help combining 2 diferent pieces of code together.
Below is code to delete rows that contain "invalid" in particular cells.
Private Sub Worksheet_Activate()
Dim MyRange As Range
Dim ThisCell As Range
Dim SearchVal As Boolean
Dim LastRow As Long
Dim r As Long
Set MyRange = Range("I1:I" & Range("I65536").End(xlUp).Row & ",M1:M" & Range("M65536").End(xlUp).Row)
For Each ThisCell In MyRange
If UCase(ThisCell.Value) = "INVALID" Then
If MsgBox("Invalid Blanket Wash data has been found. (ie. crawl washes, washes on shutdown) Would you like to delete these entries?", vbQuestion + vbYesNo, "Invalid Data Found") = vbYes Then
GoTo DeleteThem
Else
Exit Sub
End If
End If
Next ThisCell
Exit Sub
DeleteThem:
If Range("I65536").End(xlUp).Row > Range("M65536").End(xlUp).Row Then
LastRow = Range("I65536").End(xlUp).Row
Else
LastRow = Range("M65536").End(xlUp).Row
End If
For r = LastRow To 1 Step -1
If UCase(Range("I" & r).Value) = "INVALID" Or UCase(Range("M" & r).Value) = "INVALID" Then
Rows(r).EntireRow.Delete
End If
Next r
End Sub
********************************************************
Now I want to use a progress bar with that procedure and the code is as follows.
Sub ShowUserForm()
UserForm1.Show
End Sub
Sub Main()
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single
Application.ScreenUpdating = False
' Initialize variables.
Counter = 1
RowMax = 100
ColMax = 25
' Loop through cells.
For r = 1 To RowMax
For c = 1 To ColMax
'Put a random number in a cell
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c
' Update the percentage completed.
PctDone = Counter / (RowMax * ColMax)
' Call subroutine that updates the progress bar.
UpdateProgressBar PctDone
Next r
' The task is finished, so unload the UserForm.
Unload UserForm1
End Sub
Sub UpdateProgressBar(PctDone As Single)
With UserForm1
' Update the Caption property of the Frame control.
.FrameProgress.Caption = Format(PctDone, "0%")
' Widen the Label control.
.LabelProgress.Width = PctDone * _
(.FrameProgress.Width - 10)
End With
' The DoEvents allows the UserForm to update.
DoEvents
End Sub
******************************************************
Can someone help me mesh these 2 together?
Thanks, Stevegr
Below is code to delete rows that contain "invalid" in particular cells.
Private Sub Worksheet_Activate()
Dim MyRange As Range
Dim ThisCell As Range
Dim SearchVal As Boolean
Dim LastRow As Long
Dim r As Long
Set MyRange = Range("I1:I" & Range("I65536").End(xlUp).Row & ",M1:M" & Range("M65536").End(xlUp).Row)
For Each ThisCell In MyRange
If UCase(ThisCell.Value) = "INVALID" Then
If MsgBox("Invalid Blanket Wash data has been found. (ie. crawl washes, washes on shutdown) Would you like to delete these entries?", vbQuestion + vbYesNo, "Invalid Data Found") = vbYes Then
GoTo DeleteThem
Else
Exit Sub
End If
End If
Next ThisCell
Exit Sub
DeleteThem:
If Range("I65536").End(xlUp).Row > Range("M65536").End(xlUp).Row Then
LastRow = Range("I65536").End(xlUp).Row
Else
LastRow = Range("M65536").End(xlUp).Row
End If
For r = LastRow To 1 Step -1
If UCase(Range("I" & r).Value) = "INVALID" Or UCase(Range("M" & r).Value) = "INVALID" Then
Rows(r).EntireRow.Delete
End If
Next r
End Sub
********************************************************
Now I want to use a progress bar with that procedure and the code is as follows.
Sub ShowUserForm()
UserForm1.Show
End Sub
Sub Main()
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single
Application.ScreenUpdating = False
' Initialize variables.
Counter = 1
RowMax = 100
ColMax = 25
' Loop through cells.
For r = 1 To RowMax
For c = 1 To ColMax
'Put a random number in a cell
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c
' Update the percentage completed.
PctDone = Counter / (RowMax * ColMax)
' Call subroutine that updates the progress bar.
UpdateProgressBar PctDone
Next r
' The task is finished, so unload the UserForm.
Unload UserForm1
End Sub
Sub UpdateProgressBar(PctDone As Single)
With UserForm1
' Update the Caption property of the Frame control.
.FrameProgress.Caption = Format(PctDone, "0%")
' Widen the Label control.
.LabelProgress.Width = PctDone * _
(.FrameProgress.Width - 10)
End With
' The DoEvents allows the UserForm to update.
DoEvents
End Sub
******************************************************
Can someone help me mesh these 2 together?
Thanks, Stevegr
