Forum Discussion
vba for - delete rows with incomplete data in a worksheet
I prepared an example but xlsm/xlsb are not allowed for the attachment.
You may download the file and include the following code by yourself for testing.
Sub Delete_Highlight_Incompleted_Row()
' To delete or highlight incompleted row
Set CS = ActiveSheet
' I prefer to use short name for sheet
x = Val(InputBox("Input the last row of data"))
CS.Range("A" & x + 1) = "END OF ROW"
' introduce the last row checker to exit for-loop
'avoid looping infinitely
y = Val(InputBox("What do you want to do?" & Chr(10) & _
"1: Delete the incompleted row" & Chr(10) & _
"2: Highlight the incompleted row"))
If y <> 1 Then y = 2 ' avoid other input for x2
CS.Range("L2").Formula = "=counta(A2)*counta(B2)*counta(C2)*counta(D2)*counta(G2)*counta(H2)"
' you may include *counta(J2) if you need to check the column J as well
' I assume that column L is free
For r = 2 To x
If CS.Range("A" & r) = "END OF ROW" Then
' CS.Range("A" & r).ClearContents
' Clear the "END OF ROW"
' You may convert it into program in your usage
Exit For
End If
CS.Range("L2").Copy Destination:=CS.Range("L" & r)
If CS.Range("L" & r) = 0 Then
' Row r is an incompleted row
Select Case y
Case 1 ' to delete the incompleted rows
CS.Range("A" & r & ":J" & r).ClearContents
CS.Range("A" & r + 1 & ":J" & x + 2).Copy Destination:=CS.Range("A" & r)
r = r - 1
' since the row is moved upward, I need to reduce the value of r
' otherwise, I cannot delete two consecutive incompleted row
Case 2 ' to highlight the incompleted rows
CS.Range("A" & r & ":J" & r).Interior.Color = RGB(255, 0, 0)
' highlight with red
' you may change rgb code on your preference
'no need to r = r - 1 as the row is not moved upward
End Select
End If
Next r
' CS.Range("L:L").ClearContents
' To delete the dummy column for checking empty row
' I make it a comment here so that you can see the effect on the column L
' If y = 2 Then
' CS.Range("A" & x + 1).ClearContents
' End If
' Clear the "END OF ROW"
' You may include this in your usage
End Sub
I have no idea to prevent the situation. Just modify the codes using an inputbox for inputting value in column G.
Private Sub worksheet_change(ByVal Target As Range)
Application.EnableEvents = False
r = Target.Row
c = Target.Column
Select Case c
Case 4
If Len(Target) = 0 Then GoTo skip_inputG
inputG:
x = InputBox("Input value in G")
If Val(x) <= 0 Then
MsgBox ("Column G requires a positive value. Please re-input")
GoTo inputG
End If
If Len(x) = 0 Then
MsgBox ("Empty input not valid")
GoTo inputG
End If
Cells(r, 7) = x
skip_inputG:
Cells(r, c + 3).Select ' select the corresponding cells in column G
Case 7
If Len(Target) = 0 And Cells(r, c - 3) > 0 Then
MsgBox ("You have to delete column D first")
Application.Undo ' undo the delete
Else
If (Val(Target.Value) <= 0 And Cells(r, c - 3) > 0) Then
MsgBox ("Input a postive value")
Target.Select ' select the corresponding cell
End If
End If
End Select
Application.EnableEvents = True
End Sub
Let's see if it is good.
Mr. Chan
It does the trick!
Your designation of 'contributor' is an understatement!
In my book you are MVP!!!
Not just because you have the skill of programming but because you go out of your way to help someone who needed it!
Thank you very very much.
wishing you more power in all of your endeavors and good health.
Mr. Chan
Thank you and best wishes...
I just tried my best to help.
Actually, I need to thank you for your question. In helping you your problem, I learn new thing which may be useful in my other excel work. Thank you.
Let's discuss any excel problem in the future. Wish you all good.