How to Check cell column A and add 1 to cell of column D by using Macro

[tlee]

Dear all,

Could anyone help how do I loop to check cells of column A if there is not
empty, then add 1 to cell of column D and stop until the cell of column A is
empty?

Thanks for your help.

tlee

 

[Patrick Molloy]

for rw = 1 to range("A65500").End(xlUp).Row
if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
next

 

[tlee]

Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

 

[Mike H]

Maybe this

Sub Marine()
Dim MyRange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
If c.Value <> "" Then
c.Offset(, 3).Value = c.Offset(, 3).Value + 1
End If
Next
End Sub

Mike

 

[Patrick Molloy]

1.go to the development environment - press and hold ALT then press F11
2. from the menu click "INSERT" then "MODULE"
3. type
SUB DEMO
4. paste into the sub the code i gave to you
5. EITHER
(a) press F5
OR
(b) go back to the work sheet. select Tools / Macro /Macros , select
Demo from the list and click "Run"

 

[tlee]

Hi Partick,

Thanks for you help again!!!

It is because it takes time for check the column A. As the result, I cannot
see the result immediately.
So, I changed End(xlup) to End(xldown).

Likes:
for rw = 1 to range("A65500").End(xldown).Row
if cells(rw,1)="" then cells(rw,"D")= cells(rw,"D") + 1
next

Then I can see the action promptly.

Thanks
tlee

 

[tlee]

Hi Mike,

Thanks for your message and help !!!

tlee

 

[Patrick Molloy]

but xlDown stops at the first blank cell in A. I deliberately used xlUp to
cover all blanks

 

[tlee]

Hi Partick,

Thanks for your recommendation.

Besides, how do it check the cell of column A when it is blank, it will
delete the row at that cell?
I think as you said it is better to use xlUP.

tlee

 

[Patrick Molloy]

the object was to check all cells in column A and put 1 into D for any row
where there was nothing in A.

my code did that.

alternative method:-

dim nn as long
nn = range("A65000").End(xlUp).Row
WITH Range("D2" & nn )
.FormulaR1C1="=IF(RC1="""",1,"""")"
.Calculate
.Value = .Value
End With

 

[tlee]

Hi Patrick,

Thanks for your help again!

Furthermore, if I want to delete the whole rows when the cell An is empty,
how can I code it?
remark n = 1 to 6500
e.g A40 is empty, then delete the whole row where A40 is located.

tlee

 

[Patrick Molloy]

you need to start with the highest row number. think about it. if A3 and A4
are blank, and you delete row 3 first, then the data that was in row 4 moves
'up' to row three, if your loop now goes to 4, you'll have skipped the blank
row thats now in row 3 ... so we check the highr rows first


application.ScreenUpdating = False
for rw = 6500 to 1 step -1
if cells(rw,"A")="" then
rows(rw).Delete
end if
next
application.ScreenUpdating = True

 

[tlee]

Hi Patrick,

Thank you for your detail explanation, I got it now.

tlee