Check row above for information if it has contents copypaste empty

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

Dave Peterson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top