Hey all,
I have a simple VBA string of code that should look at a cell range and if the cell = 0 copy that row and past it into another sheet. If the cell does not = 0 the code should skip that row (don't copy anything) and move on to the next one. Here is my current code with some notes in red.
What should I be using for this line of code?
Sub Evaluate_Data()
'Declare variables
Dim intStartRow As Long
Dim intEndRow As Long
Dim intTargetColumn As Long
Dim intCounter As Integer
'set default values
intStartRow = Range("qty_range").Row
intEndRow = Range("qty_range").Row + Range("qty_range").Rows.Count - 1
intTargetColumn = Range("qty_range").Column
For intCounter = intStartRow To intEndRow
'if the cell contains a zero value
If Len(Trim(Cells(intCounter, intTargetColumn).Value)) = 0 Or Cells(intCounter, intTargetColumn).Value = 0 Then
' paste the value
[this line works, but it pastes every cell in the range] Range("qty_range").Copy
[what I want it to do is similar to my code to hide rows (see below) but instead paste the values)
Rows(intCounter & ":" & intCounter).EntireRow.Hidden = True]
Sheet3.Range("D1").PasteSpecial Paste:=xlPasteValues
End If
Next intCounter
End Sub
Thanks,
garbour
I have a simple VBA string of code that should look at a cell range and if the cell = 0 copy that row and past it into another sheet. If the cell does not = 0 the code should skip that row (don't copy anything) and move on to the next one. Here is my current code with some notes in red.
What should I be using for this line of code?
Sub Evaluate_Data()
'Declare variables
Dim intStartRow As Long
Dim intEndRow As Long
Dim intTargetColumn As Long
Dim intCounter As Integer
'set default values
intStartRow = Range("qty_range").Row
intEndRow = Range("qty_range").Row + Range("qty_range").Rows.Count - 1
intTargetColumn = Range("qty_range").Column
For intCounter = intStartRow To intEndRow
'if the cell contains a zero value
If Len(Trim(Cells(intCounter, intTargetColumn).Value)) = 0 Or Cells(intCounter, intTargetColumn).Value = 0 Then
' paste the value
[this line works, but it pastes every cell in the range] Range("qty_range").Copy
[what I want it to do is similar to my code to hide rows (see below) but instead paste the values)
Rows(intCounter & ":" & intCounter).EntireRow.Hidden = True]
Sheet3.Range("D1").PasteSpecial Paste:=xlPasteValues
End If
Next intCounter
End Sub
Thanks,
garbour