PDA

View Full Version : [SOLVED] Stop Row from being created



jerryr0125
06-20-2017, 01:05 AM
Hi - I have the following Macro that basically takes the each row/column in the "Start" sheet and creates a single row in the "End" sheet.
The macrotest works perfect.
I simply want to skip the cells that have the value "NA" in the "Start" sheet so therefore the "End" sheet will not have any rows with the value "NA".

Any thoughts on how to accomplish this ?
It seems an if statement would do the trick that would skip over the cells with a "NA" value but a little lost on where to put this.
See attachment. Thanks - jerry




Sub macrotest()
'
' macrotest Macro
'
Sheets("Start").Select

' Creates a single row for each cell on the End sheet
'
Dim r As Range, rw
Dim arr
Dim cel
Dim i As Long, x As Long, y As Long, z As Long


x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column
Set r = Range(Cells(2, 2), Cells(x, y))
z = Application.CountA(r) - 1
ReDim arr(0 To z, 1)
For Each rw In r.Rows
For Each cel In rw.SpecialCells(xlCellTypeConstants)
arr(i, 0) = Cells(cel.Row, 1)
arr(i, 1) = cel.Value
i = i + 1
Next cel
Next rw
Sheets("End").Cells(2, 1).Resize(z + 1, 2) = arr
End Sub

mdmackillop
06-20-2017, 02:16 AM
It seems an if statement would do the trick that would skip over the cells with a "NA" value

For Each cel In rw.SpecialCells(xlCellTypeConstants)
If cel.Value <> "NA" Then
arr(i, 1) = cel.Value
arr(i, 0) = Cells(cel.Row, 1)
i = i + 1
End If
Next cel

jerryr0125
06-20-2017, 02:31 PM
thanks - perfect !