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

T

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
 
P

Patrick Molloy

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

tlee

Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee
 
M

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
 
P

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"
 
T

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
 
P

Patrick Molloy

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

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
 
P

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:D" & nn )
.FormulaR1C1="=IF(RC1="""",1,"""")"
.Calculate
.Value = .Value
End With
 
T

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
 
P

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
 

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