Solved: Automatically Add Zeros in a cell

[U_Shrestha]

Hi all,
I copy and paste data from a website, the website offers to see the report in xls format, so I select and copy the row and paste it in my Workbook named "ABC".
My problem is, after I paste the values from the source .xls format, I want to use a macro that will automatically add '0' in Column B; when I paste the values, the number appears as '803987' but I want it to appear as '0803987'.
Is it also possible to add a serial number in Column A, as I keep adding the rows? Any help would be big help for me. Thanks.

Note: I take data from this page (http://www.dec.ny.gov/cfmx/extapps/d...x.cfm?pageid=2)
by putting a date range, e.g. 7/1/08 to 7/7/08 and then open the data as .xls file.

[figment]

if what you want is a 7 digit number in column B then you can do it with formatting, select all of column b and go to format cells and select custom. then enter 0000000 as your custom type.

[U_Shrestha]

Thanks again for your help figment. Is there any way to automatically add serial no. in Column A, whenever a new row is added? I used the formula '=row(a1)' and dragged down; it automatically enters a serial number in column A, but when i use autofilter, the numbers do not appear serially, is there a neater way to do this? Thanks.

[grichey]

Thanks again for your help figment. Is there any way to automatically add serial no. in Column A, whenever a new row is added? I used the formula '=row(a1)' and dragged down; it automatically enters a serial number in column A, but when i use autofilter, the numbers do not appear serially, is there a neater way to do this? Thanks.

autofilter is going to just do a sort more or less for whatever your filtering on. You're saying you want to have say 10 rows and add autofilter which may display row 3 5 7 and 9 and want them to be numbered 1 2 3 4 ? Correct?

[U_Shrestha]

You're saying you want to have say 10 rows and add autofilter which may display row 3 5 7 and 9 and want them to be numbered 1 2 3 4 ? Correct?

Yes

[grichey]

Hmm interesting. Here's how I would try doing it (but I unfortunately am swamped at work this week to actually write this). I would first record a macro of myself using autofilter as I've never needed it in a macro. I would then use that to pass whatever the vba equiavlent of the autofilter term is to a variable and do a simple if statement where for example if you were autofilter on column A on the 'Value1', it would just put your serial number into the range("b" & A.row).

I think this wouldn't be too dificult, just a little time consuming to figure out the way vba uses autofilter.

I'd also search for the vba code to do on command autofilter so you can put it all in the ThisWorkbook macro ;-).

Give it a go. I will try to have a look later this week if you're still stuck.

Good Luck with what should be an interesting little maco.

[Ken Puls]

Honestly, I don't think you even need the autofilter. Run the following routine after each time you set/unset/modify the filter:

[vba] Dim cl As Range
Dim lCount As Long
With ActiveSheet
For Each cl In .Range("A4:A" & .Range("B" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
lCount = lCount + 1
cl.Value = lCount
Next cl
End With[/vba]

HTH,

[U_Shrestha]

Thank you very much for your code Ken Puls. That works great for me.

Thank you all