Can you use offset and empty in the same code
 

Imported csv causes my headings to be off on the rows as well as the columns.
As part of my code attempting to realign the column headings, I have the
following code to search column B for any instance of a certain word and copy
it to column B of the previous row. I now have 2 rows consecutively that
have that word in it. I need to be able to empty every other instance of the
word beginning at row 3. Is there a way to copy then empty...search again
and copy then empty or does it make more sense to copy all instances and loop
through again and empty every other instance?

Sub MoveGoals()
Dim Cell As Range
For Each Cell In Range("B:B")
If Cell.Value = "GOALS" Then
Cell.Offset(-1, 0) = "GOALS"
End If
Next Cell
End Sub

Can you use offset and empty in the same code
 

If I were doing it, I would empty as I go. Assuming you really intend to move
the found data up one row:

Sub MoveGoals()
Dim Cell As Range
For Each Cell In Range("B:B")
If UCase(Cell.Value) = "GOALS" Then
Cell.Cut Cell.Offset(-1, 0)
End If
Next Cell
Application.CutCopyMode = False
End Sub

You might want to run this on a test sheet first.



"jeremiah" wrote:

Imported csv causes my headings to be off on the rows as well as the columns.
As part of my code attempting to realign the column headings, I have the
following code to search column B for any instance of a certain word and copy
it to column B of the previous row. I now have 2 rows consecutively that
have that word in it. I need to be able to empty every other instance of the
word beginning at row 3. Is there a way to copy then empty...search again
and copy then empty or does it make more sense to copy all instances and loop
through again and empty every other instance?

Sub MoveGoals()
Dim Cell As Range
For Each Cell In Range("B:B")
If Cell.Value = "GOALS" Then
Cell.Offset(-1, 0) = "GOALS"
End If
Next Cell
End Sub

Can you use offset and empty in the same code
 

Exactly. Thanks again, it works perfectly.

"JLGWhiz" wrote:

If I were doing it, I would empty as I go. Assuming you really intend to move
the found data up one row:

Sub MoveGoals()
Dim Cell As Range
For Each Cell In Range("B:B")
If UCase(Cell.Value) = "GOALS" Then
Cell.Cut Cell.Offset(-1, 0)
End If
Next Cell
Application.CutCopyMode = False
End Sub

You might want to run this on a test sheet first.



"jeremiah" wrote:

Imported csv causes my headings to be off on the rows as well as the columns.
As part of my code attempting to realign the column headings, I have the
following code to search column B for any instance of a certain word and copy
it to column B of the previous row. I now have 2 rows consecutively that
have that word in it. I need to be able to empty every other instance of the
word beginning at row 3. Is there a way to copy then empty...search again
and copy then empty or does it make more sense to copy all instances and loop
through again and empty every other instance?

Sub MoveGoals()
Dim Cell As Range
For Each Cell In Range("B:B")
If Cell.Value = "GOALS" Then
Cell.Offset(-1, 0) = "GOALS"
End If
Next Cell
End Sub