![]() |
#1
|
|||
|
|||
![]()
There are some 38,000 cells on this spreadsheet I'm working with; and some contain values and some are empty. I need a quick-and-easy method to take any cell that doesn't have a value in it, and stick a value of "0" in it, so that I can upload the spreadsheet to a table that doesn't allow nuls. I know this must be very easy; but I can't seem to get my brain around it today.
Help! thanks d |
#2
|
|||
|
|||
![]()
Well, you can click on the block in the upper left corner of the worksheet (left of the A, above the 1, in your row and column titles). That will select the whole worksheet.
Next go to the Edit menu and select "Replace..." In the "Find what" don't enter anything. In the "Replace with" enter a zero. Press the "Replace all" button. However, you will note that this will only fill in zeros to the "last cell" that you have populated. ================================================== =============== If this is not acceptable then you will need to go to the last poplulated cell in your worksheet. Select it. Press the CTRL+DOWN-ARROW keys, which will take you to row 65536. Then press the CTRL+RIGHT-ARROW keys, which will take you to column IV. Put a 0 in this cell. Then do the above again. This will start populating every applicable cell with a zero. After it has gone a few seconds, press ESC. Then just copy and paste a complete row of zeros down to the bottom (using CTRL+SHIRT+DOWN-ARROW). But be aware that this will generate a workbook that is a minimum of 95mb in size. Hopefully, the first solution will work and you only have to import a range and not all cells in the worksheet. Regards, Andrew |
![]() |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find and Replace Macro - A Better Way | Tribos | Word VBA | 0 | 10-08-2008 03:22 AM |
![]() |
M Skabialka | Word | 2 | 06-06-2006 10:05 AM |