Help Explaining Code

[Dazzawm]

I have used my limited knowledge of VBA and put this code together from various sources. It seems to do what I need it to do, by looking in column C and whenever there is a blank cell it deletes the entire row.

Would someone please explain each step for me please so I can get my head around it and aid my learning.

Thanks

Sub test()
Application.ScreenUpdating = False
Dim a As Integer
Dim b As Integer
Range("c1").Select
a = ActiveCell.CurrentRegion.Rows.Count
For b = 1 To a
If Selection.Value = "" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Next b
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[Sthrncali]

Here you go:

Sub test()

Application.ScreenUpdating = False 
'This line tells excel NOT to redraw the screen after each cell change. This line is pretty standard in my VBA routines.

Dim a As Integer
Dim b As Integer
'Defines two variables, a and b, and tells excel the data type will be Integers (Which are whole numbers. In this macro they are used to describe Row numbers.
 

Range("c1").Select
'Moves the cursor to Cell Address C1
 

a = ActiveCell.CurrentRegion.Rows.Count
'Sets the variable "a" which we defined earlier to count the total number of rows. 

For b = 1 To a
'This is a counter. It is saying For 1 to XXX (However many rows of data you have... i.e. Do something for Row 1, then Row 2, Then Row 3, and so on.
 

If Selection.Value = "" Then
' Tests whether the current cell is blank. 
 

Selection.EntireRow.Delete
'If XXX Then XXX --- If statement is true, do something. In this case, delete the entire row since the cell value was blank.

Else
Selection.Offset(1, 0).Select
' If Statement is False, Do something else. In this case, move down 1 cell.
End If
 

Next b
' Progress the counter up 1. So b = 1, b = 2, b = 3... I.e. Row 1, Row 2, Row 3. Etc.

Range("A1").Select
'At the end of the routine, moves the cursor back to Range A1

Application.ScreenUpdating = True
'Turns screen updating back on. At this point in the code you would be able to see the updated cells on your screen.

End Sub

I hope that helps.

 

[Dazzawm]

When you set variables, and use integer and long for numbers, string for text etc what do you use when there are numbers and text within a cell?

 

[Dazzawm]

Also on this line Else
Selection.Offset(1, 0).Select
' If Statement is False, Do something else. In this case, move down 1 cell.
End If
How does it know to delete row if cell is blank?

 

[Sthrncali]

If there is text you set it as a String.

 

[Sthrncali]

If Selection.Value = "" Then
' Tests whether the current cell is blank.
Selection.EntireRow.Delete

= "" is the equivalent of blank.

 

[Dazzawm]

If there is text you set it as a String.

Thanks, I know that bit what I asked was what if the cell is a mixture of text and numbers?

 

[Dazzawm]

If Selection.Value = "" Then
' Tests whether the current cell is blank.
Selection.EntireRow.Delete

= "" is the equivalent of blank.

I know that part also!! What I asked was if the answer was false and it was to move down a cell how does it know then to delete the row?

 

[Sthrncali]

If there is any text at all then you would use the String data type and any evaluations/logic against that data would have to be text orientied. Now if you wanted to use multiple data types in the same variable, you declare it to be a "Variant", but that doesn't work if an individual piece of data is MIXED. i.e.

Cell A1 = h4j5h667h
Cell A2 = hjh45jkh67
Cell A3 = 2233hh5h6

That data set would be declared a STRING as there is text in every cell of the range.


Cell B1 = 1234
Cell B2 = Test
Cell B3 = 45

That data set could be declared a VARIANT as their are different data types within the range.

The thing with variables is, most data sets can be declared multiple different types of variable, depending on how you want to use the data set. I.E. 455 could be used as a string, or as an integer, or as a Variant. It all depends on what your trying to accomplish.

Part II

"If the answer was false and it was to move down a cell how does it know then to delete the row?"- It doesn't delete a row in this instance.

In YOUR code, the logic test requires it to be TRUE (or blank) to delete a row. Otherwise the code steps forward 1 row and runs the logic test again.

 

[Dazzawm]

Thanks for all your help.