Table Convert To Range

[Jared_Jones_23]

Right now the data I am using is stored in a table. When its like this it will not let me copy a row and insert it but if you convert it to a range it works fine. However, once I convert it to a range and run the following macro it deletes a row of information that isn't even in the range thats being altered. It happens in the red text and that is using the cells from row 20 down but my information in row 19 gets erased. Any ideas?
Thank you,
Jared

Sub Macro_1() 'Highlights empty mandatory cells
Dim myarray As Variant
Dim lastRow, lastCol As Integer
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
myarray = Array(1, 2, 7, 8, 12, 14, 15, 16, 18, 20, 21, 22, 23, 24, 25, 26) 'mandatory columns
count = 0

With ActiveSheet
lastRow = .Cells(.Rows.count, "D").End(xlUp).Row 'Finds last row and column
lastCol = .Cells(20, .Columns.count).End(xlToLeft).Column
End With
Range(Cells(20, 1), Cells(lastRow, lastCol)).Interior.ColorIndex = 0
For r = 20 To lastRow 'Finds empty mandatory cells and colors them
If Cells(r, 4) <> "" Then
For Each xVal In myarray
If Cells(r, xVal) = "" Then
Cells(r, xVal).Interior.ColorIndex = 28
count = count + 1
End If
Next xVal
End If
Next r
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub <!-- / message -->

 

[GlennUK]

What's in row 19 before the macro runs? Do you step through the macro and see when it get's cleared?

 

[Jared_Jones_23]

Row 19 is the titles for each column that the data goes in. For example there is customer, country, state, amount and so on. The row is being deleted by the second line of red text in my code i posted.
Thank you,
Jared

 

[Weaver]

How come you're checking the contents of column 4?

If all you need to do is shade mandatory cells that have no data (are they completely clear or might they have a formula that evaluates to a null string) then maybe a conditional format could be more useful? I notice you count the empty cells but it appears you don't use the count for anything?

 

[Jared_Jones_23]

I check column 4 because that is the column I want to check to see if it is filled in. Then any row with data in column 4 is then checked for the mandatory cells (the array). I have a counter in there because it displays a Message Box if the count is more than one (if there is a cell that needs filled in)