Posts by [the blue fox]

    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 ..

    Re: Delete Empty Rows

    I have sorted .. but want sort Descending .. not Ascending.

    If sorted B5:AK10804 with Column B Descending .. the blank lines appear first ..

    Hence wanting to delete blank rows ranged between B5:B10804

    Hope this makes sense.

    Re: Deleting Blank Rows Within Set Range

    sorry to be difficult .. but not aure where in my code that line has to go.
    Tried it in a couple of places and it throws an error.

    Can you suggest a code to remove the blank lines b4 they copy into the main sheet?


    Re: Deleting Blank Rows Within Set Range

    umm .. not sure what I did wrong .. but that kinda did nothing.

    To explain a little more ..
    I have 12 sheets that collect data. I then have a macro to copy all 12 sheets onto a main data page.
    Once this has been done, there are blank rows.

    What I require at the bottom of my code .. is to search between ROW 5:10804 for any blank rows and delete them .. then sort the data descendingly from Column B.

    Here is my code so far.

    Hope this makes sense

    Morning all,

    I have read all the tutorials and examples of how to delete rows IF the row contains no data within a worksheet or workbook.

    However .. I don't want all rows deleted, just rows within a set range.
    I can't find any reference to deleting blank rows within a range, just the entire workbook or worksheet.

    Can anyone help?

    Re: Toggle Column Visiblity & Button Caption

    Sorry guys ..

    Is there any way to get around having to assign the button number?
    I have the same button on 12 sheets .. and even though they are a copy of each other from Button 2 .. its throwing up an error.

    Can it be a generic code?

    On creating a button to perform a macro, we are hiding columns.

    Can the macro pick up that

    If the columns E:J are hidden, then the text on the button to read 'Unhide'
    If the columns E:J and unhidden, then text on the button to read 'Hide'

    I am aware I can change what is written on the button via a macro, but can it see that the columns are hidden????

    Make sense I hope


    Re: Conditional Formatting: Greater Than 3 Conditions

    Hi ..

    I have played with this coding .. but am not sure how this applies to what I am trying to do.

    I do not need multiple colours nor multiple aruguments as shown in the coding. So I have removed the multiple colour lines, now I get B5:B254 to equal a green cell if the cell = 1 to 999999, though now how do i continue to change B5 to normal colour and back to green depending on what is put into the other columns?

    Maybe I am not understanding how this code applies to my request.

    I only want the cell B5 to change to one single colour based on those other columns have a value greater than 1.

    Hi Everyone ..

    I have a cell that I want changed to green (red 133 Green 180 Blue 0) if a line is considered active. As conditional formatting only allows 3 arguments, I need to perhaps code it?

    Along the columns are cells that can change a row to active or inactive.
    Here are my arguments.

    If B5>1 .. row = active
    If B5>1 & G5>1 .. row = inactive
    If B5>1 & G5>1 & H5>1 .. row = active

    If at any time L5>1 & M5="" .. row = inactive .. OR
    If at any time Q5>1 & R5="" .. row = inactive .. BUT

    If at any time L5>1 & M5>1 .. or .. Q5>1 & R5>1 .. row = active

    then ..

    If H5>1, M5>1, R5>1, T5>1 & AH5="", AJ5="", AL5="" .. row = active

    so If at any time AH5>1 .. or .. AJ5>1 .. or .. AL5>1 .. row = inactive

    Hope this makes sense to someone.
    More than happy to send a copy of the spreadsheet if it makes it any easier.


    Re: Removal Of Time Stamp

    I have a formular on another sheet to count all the dates that are the 15 Aug 06 for example and as there is a date in the fx bar, it is not picking it up as a count.

    Re: Removal Of Time Stamp

    in the cell it reads

    15 Aug 06

    In the fx bar it reads

    15/08/2006 8:32:05 AM

    I need the fx bar to read 15 Aug 06.
    Even when I format the cells to be dd mmm yy or dd mmm .. it still retains the time in the fx bar.

    Hope this makes sense.

    Re: Removal Of Time Stamp

    I have highlighted the column and formatted the data, but for some reason, although the cell says on 17 Jul 06, the inside of the cell still has the time

    Any ideas?

    Hi All.

    In a previous version of a spreadsheet I created, when a value was placed in 'B5' .. C5 would then populate with an automatic 'NOW'.

    However, I didn't know at the time, procuded the cell to read not only the date, but also a time. 17 Jul 06 12:38:45

    I have since change the auto code to DATE instead of NOW, but was wondering if there was anyway to changed the already populated cells so that it did not show the time without having to manually enter it again.


    Hi Everyone ..

    I am using the code below to auto input a date once another cell has had a value entered into it.
    However .. the code seems to display the date WITH the time.
    I have formatted the cells, but if you place your cursor in the cell, the value is the date & the time.

    How do I populate cell 'C' with the date only in the format I have specified WITHOUT the time when cell 'B' has a value entered.


    Re: Protecting Sheet with VBA Code

    Here is what is set into the worksheet.
    Now when I protect the sheet .. this doesnt work.
    The DEBUG highlights in yellow the line ..

    .Value = Now

    Can this not be run if you have protected the sheet?

    Hi All ..

    Having a problem with protecting a sheet and then the VB Coding doing a flip.

    I have set so that if a number is entered into Column B .. then it will auto populate the 'NOW' date into column C.

    But when I protect my sheet .. it wont do it.
    I have ticket 'Formate Cells' .. but still doesnt seem to work.

    Any ideas?