Previous Topic Next Topic

VBA Excel Search Select Paste Delete

Solved/Closed
Clint16 Posts 2 Registration date Monday November 2, 2009 Status Member Last seen November 4, 2009 - Nov 3, 2009 at 04:00 AM
Clint16 Posts 2 Registration date Monday November 2, 2009 Status Member Last seen November 4, 2009 - Nov 4, 2009 at 07:07 AM
Hello,

If someone could possible help on this I would be very greatful.

I have reems of data in Excel (tidal data). I'm looking to get the highs/lows taken out fo the data.

I've worked out a way to have a moving average, but now need to create a VBA macro which will:

1) Search for a specific value (say 0 - my moving average puts a 0 next to specific rows in the data).
(maybe there is a better way to look for the higs and lows?)

2) copy the row (or 4 values from it) to the left of the 0 found

3) Past them in a new sheet, or different location - say the top on the right of all the data, or specified area.

4) not have empty rows in the pasted areas (so I gues delete empty row in this area).

I've been playing with VBA now, but struggling.

So far I;ve got:

Sub processcells2()
For Each cell In slecetion
If cell.Value = 0 Then
ActiveCell.Offset(0, -3).Range("a1:b1").Copy Range(ActiveCell.Offset(0, 4))
End If
Next cell
End Sub

With (but this deletes all the empty rows in the sheet) So not really helping.

Sub DeleteEmptyRow()

LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For x = LastRow To 1 Step -1
If Application.CountA(Rows(x)) = 0 Then Rows(x).Delete
Next x
End Sub


But this is not working. I was hoping to compile all the small parts into one operation.

Thanks you for any assistance possible. Clint 16

1 response

Answer 1 / 1
Clint16 Posts 2 Registration date Monday November 2, 2009 Status Member Last seen November 4, 2009
Nov 4, 2009 at 07:07 AM
It appears I've got it sorted out:

Sub main()
startrow = Cells(1, 7)
endrow = Cells(2, 7)
col = Cells(3, 7)
col1 = Cells(4, 7)
col2 = Cells(5, 7)
Call FHL(startrow, endrow, col, col1, col2)
End Sub
Sub FHL(row1, row2, col, col1, col2)
wrow = row1
For I = row1 To row2
If Cells(i, col).Value = 0 Then
For j = col1 To col2
Cells(wrow, col + col2 - col1 + 1 + j) = Cells(i, j)
Next j
wrow = wrow + 1
End If
Next
End Sub
0