remove zeroes macro

[kylefoley76]

This macro is not work. It is supposed to copy all cells from b to cb, then down four rows and paste the value, then go back and replace all cells which have a zero in them with a blank. instead it will copy all cells from b to cb, copy it, then paste that in the next three rows down.

also if someone could rewrite the macro so that columb B through CB are clearly visible. I don't know why people often name the column after the number they appear in the alphabet, i always have to count which letter it is, very confusing. thanks in advance.

Dim Cell As Range
Dim RngFrom As Range
Dim RngTo As Range

Set RngFrom = Selection
Set RngTo = Cells(RngFrom.Row + 1, 2).Resize(15, 78)

Cells(RngFrom.Row, 2).Resize(, 78).Copy Destination:=RngTo

For Each Cell In RngTo
If Cell.Value = 0 Then
Cell.Value = ""
Else
Cell.Value = Cell.Value
End If
Next Cell

RngTo.Activate

Set Cell = Nothing
Set RngFrom = Nothing
Set RngTo = Nothing
End Sub

 

[Rick Rothstein]

Does this code do what you want?

Sub CopyAndRemoveZeroes()
  Dim LastRow As Long
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Range("C1:CB" & LastRow).Copy Cells(LastRow + 4, "C")
  Cells(LastRow + 4, "C").Resize(LastRow, 78).Replace 0, "", xlWhole
End Sub

 

[kylefoley76]

thanks for helping me out, but that macro didn't work. It copied what was in c1:cb4, then went down four rows and pasted it down there.

also i need the macro to start with whatever cell i have currently selected, because every four days i need to go down four rows.

i need the macro to select all cells from c to cb and down 4 rows, copy them, paste them, then replace zeroes with blank. thanks

 

[Rick Rothstein]

thanks for helping me out, but that macro didn't work. It copied what was in c1:cb4, then went down four rows and pasted it down there.

also i need the macro to start with whatever cell i have currently selected, because every four days i need to go down four rows.

i need the macro to select all cells from c to cb and down 4 rows, copy them, paste them, then replace zeroes with blank. thanks

First off, if it only copied C1:CB4, then that is because the last piece of data on the sheet was located in Row 4. While your layout is second nature to you, you have to remember that none of us here have seen it, so we are dependent on your description. Unfortunately your code seemed to indicate a layout slightly different than your written description, so I tried to implement your written descriptions. Having apparently failed in that attempt, I think we (or at least I) will need to see your layout. One possibility is for you to paste your Excel data to the forum...
MrExcel HTML Maker or Excel Jeanie. The other is to post your workbook to one of the free file sharing websites

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

 

[kylefoley76]

i'm a bit of a hurry, and i won't be able to sit down and learn how to use excel html for a few days. i can't use excel jeanie because that's for windows and i have a mac.

thanks for your help, but i can't upload the data right now. i can't imagine that

i need the macro to select all cells from c to cb and down 4 rows, copy them, paste them, then replace zeroes with blank. thanks

is very hard to understand. if you please try one more time, otherwise i'll have to delay this problem for about a week, until i can find time to work on learning how to use excel html. thanks

 

[Rick Rothstein]

if you please try one more time, otherwise i'll have to delay this problem for about a week, until i can find time to work on learning how to use excel html. thanks

I'm not sure what you mean by needing to learn excel html... the website links I gave you do not require you to learn anything, just register and upload your file, then post the URL they give you. I'd be happy to try again, but you will need to give a more detailed description of your layout and exactly what part of it you want copied and where. For instance, do you have many contiguous rows of which you only want the last four rows copied? Or do you have lots of rows in groups of 4 rows each with one or more (how many) blank rows separating them? Is your data all constants or all formulas or a mixture of both? The more detail you give, the better able we will be able to form a mental picture of your layout.

 

[kylefoley76]

ok i thought of a better way to explain what i mean. this is what it should look like at first

all the cells have forumlas in them. i then copy all of the cells, paste values, then replace the zeroes with blanks. like so

the macro needs to start at whatever cell in the c column is selected and do this for the next four rows and the column out to column CB

 

[Rick Rothstein]

Okay, does this macro do what you want then?

Sub CopyAndRemoveZeroes()
  Dim LastRow As Long
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  ActiveCell.Resize(4, 78).Copy Cells(LastRow + 4, "C")
  Cells(LastRow + 4, "C").Resize(4, 78).Replace 0, "", xlWhole
End Sub

 

[kylefoley76]

I really appreciate your help, but that macro didn't remove any of the zeroes on my spreadsheet.

 

[Rick Rothstein]

I really appreciate your help, but that macro didn't remove any of the zeroes on my spreadsheet.

Okay, then I guess you have formulas mixed in your cells. Try this version of my code instead then...

Sub CopyAndRemoveZeroes()
  Dim LastRow As Long
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  ActiveCell.Resize(4, 78).Copy
  With Cells(LastRow + 4, "C")
    .PasteSpecial xlPasteValues
    .Resize(4, 78).Replace 0, "", xlWhole
    .Cells(1, 1).Select
  End With
  Application.CutCopyMode = False
End Sub