Macro to skip rows that have blank cells

[Patrick340]

How NOT to run my macro by skipping the rows that have blanks cells from: M2 to M20?
The macro runs great, except I do not want the blank cells that are in column M to move to J. After I run the macro it should move the numbers in cells from column M to J.


I tried if Range(M2:M20>.Value>=0 then macro
end if it is not working.

Here is my macro:


Sub Test30()
Dim x As Integer


For x = 2 To 20
'Write B2:J2 into A2:I2
Range("A" & x).Resize(, 9).Value = Range("B" & x).Resize(, 9).Value
'Write M2 into J2
Range("J" & x).Value = Range("M" & x).Value
'Clear M2
Range("M" & x).ClearContents
Next x
End Sub

The columns A to J are for ten weeks of scores and column M is the 11th week and after running the macro it becomes the 10th week.


Windows 10, Ms Office 2013, Excel

Thanks for your help,

Patrick

[p45cal]

You could give this a try:

Sub Test30()
Dim x As Integer

For x = 2 To 20
  If Not IsEmpty(Range("M" & x)) Then
    'Write B2:J2 into A2:I2
    Range("A" & x).Resize(, 9).Value = Range("B" & x).Resize(, 9).Value
    'Write M2 into J2
    Range("J" & x).Value = Range("M" & x).Value
    'Clear M2
    Range("M" & x).ClearContents
  End If
Next x
End Sub

or depending on exactly what you want perhaps:

Sub Test30()
Dim x As Integer

For x = 2 To 20
  'Write B2:J2 into A2:I2
  Range("A" & x).Resize(, 9).Value = Range("B" & x).Resize(, 9).Value
  'Write M2 into J2
  If Not IsEmpty(Range("M" & x)) Then
    Range("J" & x).Value = Range("M" & x).Value
    'Clear M2
    Range("M" & x).ClearContents
  End If
Next x
End Sub

(both untested)

[Patrick340]

Your first suggestion is exactly what I was looking for.
Thankyou very much p45cal.

patrick340