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

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

"tlee" wrote in message
...
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

Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

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

"tlee" wrote in message
...
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

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

"tlee" wrote:

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

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" wrote in message
...
Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

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

"tlee" wrote in message
...
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

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


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" wrote in message
...
Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

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

"tlee" wrote in message
...
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

Hi Mike,

Thanks for your message and help !!!

tlee


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

"tlee" wrote:

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

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

"tlee" wrote in message
...
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


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" wrote in message
...
Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

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

"tlee" wrote in message
...
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

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


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

"tlee" wrote in message
...
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


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" wrote in message
...
Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

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

"tlee" wrote in message
...
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






-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com

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




"tlee" wrote in message
...
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


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

"tlee" wrote in message
...
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


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" wrote in message
...
Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

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

"tlee" wrote in message
...
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

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

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




"tlee" wrote in message
...
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


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

"tlee" wrote in message
...
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


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" wrote in message
...
Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

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

"tlee" wrote in message
...
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

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" wrote in message
...
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

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




"tlee" wrote in message
...
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


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

"tlee" wrote in message
...
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


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" wrote in message
...
Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

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

"tlee" wrote in message
...
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

Hi Patrick,

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

tlee

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" wrote in message
...
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

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




"tlee" wrote in message
...
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


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

"tlee" wrote in message
...
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


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" wrote in message
...
Hi Patrick,

Thanks for your message first.

But, it is not respond. Can help again?

tlee

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

"tlee" wrote in message
...
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