Hello,
How would I fill a selected row that has empty cells with values of zeros? Also, how would I search for a row filled with zeros and delete it? Both is necessary...any ideas?
Thank you!
Hello,
How would I fill a selected row that has empty cells with values of zeros? Also, how would I search for a row filled with zeros and delete it? Both is necessary...any ideas?
Thank you!
Try this technique:
•Select the range of cells to be tested for blanks
•From the main menu: Edit>Go To (or press the [F5] key)
•Click the [Special Cells...] button
•Select Blanks and Click OK
That should select all blank cells in the tested region.
•Type a 0 (zero)
•Hold down the [Ctrl] key and press [Enter]
That will put a zero in every selected cell.
Does that help?
Regards,
Ron
Regarding the deletion of rows with all zeros:
Method 1:
•Data>Filter>Autofilter
•Set every col criteria to 0 (zero)
•Select the displayed cells
•Edit>Delete
Excel will only allow you to delete entire rows in autofiltered lists
•Data>Filter>Autofilter (to remove the autofilter)
Method 2:
•Create a helper column to the right of the data with this formula, assuming your data is in columns A through E:
=COUNTIF(A2:E2,0)=5
•Copy that formula down as far as needed
•Now, use the same Autofilter technique as above, but only set the critera on the helper column
Does that help?
Regards,
Ron
Hi all,
Well, not all the cells I suppose. I really only need from A - X. Right now now, I find the last cell used in a column, move down 1 cell, and then I select the entire row so that I can fill these with a zero. However, I only need A - X filled, but I was thinking that it might be easier if we just filled the entire row. Whatever is easiest for you all. Any help would be appeciated. Right now I have this:
Range("A65536").End(xlUp).Cells(2, 1).Select Selection.EntireRow.Select 'Now I need to fill this row with 0s or fill the range A - X with 0s
Thanks all!
Also, there are no formulas, just data. Thanks!
Range("A65536").End(xlUp).Cells(2, 1).Select
Selection.Resize(1,24).Value = 0
Possibly for eliminating cells with all zero:
Range("A65536").End(xlUp).Select
for i = selection.row to 2 step -1
if application.Sum(cells(i,24)) = 0 then
rows(i).Delete
end if
Next
--
Regards,
Tom Ogilvy
"limshady411" <[email protected]>
wrote in message
news:[email protected]...
>
> Also, there are no formulas, just data. Thanks!
>
>
> --
> limshady411
> ------------------------------------------------------------------------
> limshady411's Profile:
http://www.excelforum.com/member.php...o&userid=28711
> View this thread: http://www.excelforum.com/showthread...hreadid=484850
>
Do you mean all 256 cells in a row?
If you mean filled with zero's, do you mean all 256 cells are filled with
zero. If not, do all cells contain a number or are blank?
If blank are the cells actually empty, or do they contain formulas that may
make the cell appear blank?
Do the cells contain formulas?
--
Regards,
Tom Ogilvy
"limshady411" <[email protected]>
wrote in message
news:[email protected]...
>
> Hello,
>
> How would I fill a selected row that has empty cells with values of
> zeros? Also, how would I search for a row filled with zeros and delete
> it? Both is necessary...any ideas?
>
> Thank you!
>
>
> --
> limshady411
> ------------------------------------------------------------------------
> limshady411's Profile:
http://www.excelforum.com/member.php...o&userid=28711
> View this thread: http://www.excelforum.com/showthread...hreadid=484850
>
Block the cells, then enter "0" in the first one and press CTRL+ENTER
The second part sounds like a macro to me. Maybe someone else can offer
suggestions.
"limshady411" wrote:
>
> Hello,
>
> How would I fill a selected row that has empty cells with values of
> zeros? Also, how would I search for a row filled with zeros and delete
> it? Both is necessary...any ideas?
>
> Thank you!
>
>
> --
> limshady411
> ------------------------------------------------------------------------
> limshady411's Profile: http://www.excelforum.com/member.php...o&userid=28711
> View this thread: http://www.excelforum.com/showthread...hreadid=484850
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks