Clear Contents, Copy And Sort

  • Morning all.

    I have 10 separate files that each have a data input sheet.
    I have 1 master file that has all 10 sheets in it and an extra sheet to display all 10 sheets information on 1.

    The drama I am having with sorting the master sheet is that every cell in the 10 sheets has a formular to copy what in the corresponding cell within the separate file. So techincally, the cells are not blank.

    The cells range is B5:G1004 on every sheet.
    I paste 'values only' from the range of each sheet into the master sheet, but when I go to sort column B Ascendingly, it puts all the data at the bottom of the rows and there is 10000 rows on the master sheet. I cant get it to flip around.

    What I need is a way to clear the cells on the 10 sheets that do not have a value before I paste into the master sheet.

    Can anyone help.

    Range B5:G1004 .. need to delete the contents of that range that do not have a value (even though there is a formular in the cell)

    Hope someone can help ..

    Tayler [the blue fox] :thanx:

  • Re: Clear Contents, Copy And Sort

    Something like this for your different areas:

    Sub ClearACellsContentsIfEqualToZero()
    ' looking at the value in Sheet 2, cell A9, and deleting contents
    ' if it is equal to 0 (i.e., formula with no value)
        If Sheets("Sheet2").Range("A9").Value = 0 Then _
    End Sub
  • Re: Clear Contents, Copy And Sort

    Im sorry to say your reply has confused me.

    Prior to copying the data from 01 C onto the master sheet (Consumer), I need all rows in '01 C' in the range B5:G1004 that do NOT have a value to be cleared of their contents so they are blank cells.

    This is part of the code I have to do all 10 sheets.

    and so on until sheet 10.

    Tayler [the blue fox] :thanx:

  • Re: Clear Contents, Copy And Sort

    Would something like this work?

    If you're doing a paste-special, all formulas that are equal to nothing when converted to the values would then show a zero (whether you have them displayed or not). Here, I'm looking at the activeworksheet.

    I change all cells with formulas to their values. I then do a find/replace (i.e., "0" with nothing).

  • Re: Clear Contents, Copy And Sort


    Thanks for your help .. though I am not quite getting what I am trying to achieve. Not sure how to word what I am after.

    Thanks for trying

    Tayler [the blue fox] :thanx:

  • Re: Clear Contents, Copy And Sort


    [vba]Sub kTest()
    Dim s As Byte, Dest As Range, sName
    Set Dest = Sheets("Consumer").[a5]
    Application.ScreenUpdating = False
    For s = 1 To 10
    sName = Format(s, "00") & " C": Sheets(sName).Activate
    [b5:g1004].Copy: Dest.PasteSpecial xlPasteValues
    Set Dest = Dest.Offset(1000)
    With Sheets("Consumer").Activate
    On Error Resume Next
    Range([a5], [a5].SpecialCells(xlLastCell)).Replace _
    What:="0", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False
    End With
    Application.ScreenUpdating = True
    End Sub[/vba]


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!