| Home |
| Search |
| Today's Posts |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Check cell column A and add 1 to cell of column D by using Macro
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need the formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
| Need Formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
| Putting check marks in every cell in a column? | Excel Worksheet Functions | |||
| check active cell column | Excel Programming | |||
Threaded Mode
