PDA

View Full Version : [SOLVED] Macro to skip rows that have blank cells



Patrick340
02-28-2018, 08:22 AM
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
02-28-2018, 08:38 AM
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
02-28-2018, 12:25 PM
Your first suggestion is exactly what I was looking for.
Thankyou very much p45cal.

patrick340