CODEWITHSUNDEEP
excelvba
Vo Phu
Vo Phu

Reputation: 95

Change the value of a column with vba code rather than loop

I want to add "#" before each cell in a column (have more than 13000 cells), and add "000" to another column through vba excel code. I can achieve this with loop but it takes a long time when running the macro. Can anyone help me with more effective solutions?

for each cell in column
  cell = "#"& cell
  next cell

for each cell in column
   select case len(cell)
     case 2: cell = "'000" & cell
     case 3: cell = "'00" & cell
   end select
    
next cell

Desired result:

456 => #456

10 => 00010

however it runs very slowly.

Upvotes: 0

Views: 33

Answers (1)

user10316640
user10316640

Reputation:

Use display format:

Range("A1:A1300").NumberFormat = "\##"
Range("B1:B1300").NumberFormat = "00000"

This does not change the underlying value, but it will display correctly.

To read the value out use Cell.Text to retrieve a string with the leading zeros.

Upvotes: 2

Related Questions