0

in Excel I have 22000 rows with '0' formatted as text. I can select entire column and click 'convert to number'.

but excel freezes for a long time. I found a way to do it simpler:

  • insert 1 as a digit to any cell
  • copy it
  • insert to the top cell of the column of zeros
  • select till the end of column, press ctrl - alt - v for smart insert
  • select option 'multiply'

Made a macros for it. Now how do i make it work for another entire column of zeros? I press the hotkey , and it just replaces the current cell with 1

Misha Akopov
  • 12,241
  • 27
  • 68
  • 82
kaban
  • 423
  • 1
  • 5
  • 10

1 Answers1

1

You can open VBA by pressing together Alt and F11 keys. All your recorded macros will be present Under Folder VBAProject > Modules.

VBA Screenshot

You can change the required column in your macro and then again run using the shortcut or Press F5 to execute the macro.

Update: You can use the below code snippet to add custom column range, which the user will input for the macro to execute on.

Dim rngCol As Range
Set rngCol = Application.InputBox("Pick Column Range: ", , , , , , , 8)
Misha Akopov
  • 12,241
  • 27
  • 68
  • 82
stud3nt
  • 2,056
  • 1
  • 12
  • 21
  • how do i apply to current selected column? this seems like hardcoding it. macros is useful when i automate sequence of actions – kaban Dec 26 '19 at 04:29
  • Please check the updated answer in which you can have an input box to select the range on which you want to execute the macro. – stud3nt Dec 26 '19 at 04:35