Check row above for information if it has contents copypaste empty

  • Thread starter Thread starter TGalin
  • Start date Start date
T

TGalin

I am trying to get this macro

For Operation = 2 To Cells(Rows.Count, "B").End(xlUp).Row
If Len(Application.Trim(Cells(Operation, "A"))) < 1 Then Cells(Operation,
"A") = "Examine"
Next Operation

To perform these steps

Check each row in Column A for data
If the row contains data do nothing
If the row does not contain data check the row above it for data. If the
row above it has data enter the text ‘Closed’ into the empty row.

However, it will only work if data is in another Column but the same Row not
the same Column another row. Can you help?
 
Maybe...(but test it!):

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim OpRow As Long
Dim LastRow As Long 'just because I like to use a variable

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

'start at the bottom and work up
'stopping at row 3 so that row 2 isn't compared to row 1 (the header row)
For OpRow = LastRow To 3 Step -1
If Trim(.Cells(OpRow, "A").Value) = "" Then
'do nothing
Else
If Trim(.Cells(OpRow - 1, "A").Value) = "" Then
'do nothing
Else
.Cells(OpRow, "A").Value = "Closed"
End If
End If
Next OpRow
End With
End Sub
 
Back
Top