Posts by [the blue fox]

    Re: Hiding Empty Rows Without Macro Button


    I guess I am just after the easiest way to hide them without the officer who is entering data in the worksheet having to do anything .. as it's not me that will be entering the data.


    Can the code you suggested be auto run when the file is opened?


    Basically, two officer have a file to input data into .. both officers enter data into rows 5-60 in their own file .. then a master database if you like collects the data into another file. So officer 1's data will appear in rows 5-60 in the master file and officer 2's data will appear in 61-116.


    I am after the master file only to display the rows that have data in them.


    So upon opening the master file, all rows that are blank will be hidden.


    Does this make sense?
    [email protected]


    Thanks for you help Boo :thanx:

    Hi all ..


    Is there a way to hide rows within a range that do not have data in the B column of that row without using a macro button?


    The range is row 5-60 .. if data is not in column B of that row, can that row be hidden?


    If so, is this code that goes in the Sheet? or in a module?


    HELP :?

    I work for a large company that has set the standard font size, type and colour for certain styles.


    Hyperlink is set at size 10 font, blue and underlined and obviously is a link to whatever is typed in be it a website or email address.


    Because I can not change the companies template for how hyperlinks are displayed .. am I able to change it with VBA coding?


    I need to alter H5:H605.
    It currently displays as font size 10, Arial Font, Blue and underlined.
    I need it to be font size 8, Arial, black and no underline ..


    OR .. is there a way to delete hyperlink formats if a hyperlink has been entered?


    HELP .. :?

    I was given this code that works great. Now I would like to get a little more specific with WHAT opens up.


    Each column range AJ:AO, AR:AX and BA:BG has the potential to have values, so if the officer is in row 36 and there is data in AJ36, but also in AR6, both AJ:AO and AR:AX will unhide. I would only like the cloumn set to unhide if there is value in the ROW 36 or the row they are in.


    Can anyone help?


    [vba]
    Sub reassess_open()
    '
    ' reassess_open Macro
    ' Macro recorded 16/12/2005
    '
    With ActiveSheet
    If WorksheetFunction.CountA(.Range("AJ5:AJ105")) <> 0 Then
    .Range("AJ:AO").Columns.Hidden = False
    If WorksheetFunction.CountA(.Range("AR5:AR105")) <> 0 Then
    .Range("AR:AX").Columns.Hidden = False
    If WorksheetFunction.CountA(.Range("BA5:BA105")) <> 0 Then
    .Range("BA:BG").Columns.Hidden = False
    End If
    End If
    End If
    End With
    End Sub[/vba]

    Morning All ..hoping I could get some help.


    I have columns AJ-BG opening for a reassessment .. but there are 3 types of re-assessments. AJ:AO .. AR:AX .. BA:BG. I only want certain columns to UNHIDE if there is value in AJ, AR or BA.


    This is what I was given, but it doesn't work. Can anyone help out?


    [vba]
    Sub reassess_open()
    '
    ' reassess_open Macro
    ' Macro recorded 16/12/2005 by Tayler Reid
    '
    If Range("AJ5:AJ105").Value Then
    Range("AJ:AO").Columns.Hidden = False
    If Range("AR5:AR105").Value Then
    Range("AR:AX").Columns.Hidden = False
    If Range("BA5:BA105").Value Then
    Range("BA:BG").Columns.Hidden = False
    Cells(ActiveCell.Row, 35).Select
    End If
    End If
    End If
    End Sub[/vba]

    Hi all .. hope for some help on this


    I have some column hidden within a spreadsheet [a.xls] that is linked to another file [b.xls].


    I was hoping that I could find the code that if someone was to type into A1 of b.xls .. that certain columns would open up in a.xls.


    Is this possible?


    HELP :?

    Hi all .. Love some help on this.


    I have a Macro Button in Cell AF2 that opens up the next 6 columns [which are hidden]. Rows 2 & 3 have their panes frozen so it acts as a header for column identifiers.


    Each sheet has 100 rows so the officer can be in as low as row 105.


    What I want to do is when they click on the UNHIDE button from cell AG92, once the unhide function completes I would like it to go to AH92 which is the next cell along.


    I have this so far ..


    [vba]
    Sub reassess_open()
    '
    ' reassess_open Macro
    ' Macro recorded 16/12/2005'


    Columns("AG:AM").Select
    Selection.EntireColumn.Hidden = False
    Range("AH5").Select
    End Sub[/vba]


    Instead of going to AH5 I want it to go to the AH value of the row they are in.


    HELP! :?

    Re: VBA Cell Value Change


    The time will be what the time is at the moment of data entry = NOW()


    I would assume there would be something in the coding to convert all cells with a format of dd/mmm to dd/mmm hh:mm


    The hh:mm component would be NOW() function.

    I have officers putting a date within a cell.
    What I need the cell then to do is to convert to a NOW() value once they have hit return, enter or move to another cell.


    They input 15-Dec .. I need it to return (in the same field) 15-Dec 8:50.


    I have seen a few VBA samples for worksheet_change, but none seem to make any sense.


    HELP !! :?

    VBA worksheet_change - Date &amp; Time


    I was given this example to add a date and time into a cell using worksheet_change.


    As far as I have been told, once I put a value into A2, B2 should return a NOW() figure.


    I have added this code into a module, but nothing happens within B2.
    Am I doing something wrong? (well clearly if it's not working) Would be great if someone could help me out.


    Basically, what I am trying to do, if an officer puts a loan number into A2, then I wish the current date and time to be put into B2. A2 is blank open opening the workbook.


    Re: Time &amp; Date


    I can have it set so that once they enter a loan number into column A1, column B1 inserts a date and time .. via NOW() .. BUT .. if the user then goes to A2 to enter a second loan, B2 has the current time added, but A2 also updates to the current time.


    Is there anyway to set it so that A2 stays static within a formular??
    Or is the only way via VBA coding or the manual shift+; ??


    and guys .. thanks heaps for your help on this :)

    Re: Time &amp; Date


    I was unaware of those commands .. so it will help with my knowledge, but not with this situation.


    The officers that will enter the data into the field (besides being old school idiots) can not be trusted to enter time (for they can alter it to better their statistics) .. so I was hoping for a way that they could enter the date, and a time appear without their knowledge or .. appear automatically into the same field or another which I can lock.


    dd/mmm hh:ss is the format I am playing with .. but without the officer entering '14/12 9:56' into the field, I can not seem to get what I want.


    Is there anyway to enter a date into one field and the time appear in either the same field by way of cell format or another field, automatically entering the time that the 1st field was entered?

    If anyone can answer this question .. YAY.
    I have two columns within a spreadsheet. I want a date & time value in each column depending on when the field was entered.


    My problem is using NOW() .. this updates to the current time and date for all fields within the column.


    Is there anyway to add a date & time within a field and lock the values once they are entered?


    Ultimately, I would like to calculate the exact time between the two fields.


    HELP!