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
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