Posts by hgus393

    Re: Transferring values from each sheet to a new sheet


    Hum I think I am on the right way here, I am checking the number of rows that has values ie COUNTA for each worksheet and by that being able to determine how many rows to transferred (copied) to the new sheet. The problem I am facing now is getting the values into the new worksheet with all the values.. anybody know hos to do this?

    Re: Format Sheet Objects


    Does this help any?


    Code
    Sub DoesThisHelp()
    Dim MyShape
    
    
    Set MyShape = Worksheets(1)
    MyShape.Shapes(1).Fill.PresetGradient _
        msoGradientHorizontal, 1, msoGradientBrass
    
    
    End Sub


    //Robert

    Re: Sum based on row count



    Nearly..The first cell has a non numeric value and as such this macro returns
    a ##### value, tired replacing the [1] by [-1] but it does not work....

    Hello All,


    Is is possible to create a sum function on the active cell that checks the number of rows as its base?
    I have tried the following but it does not work...anybody knows if it is possible to use this method?


    Code
    Sub SpecialSum()
    
    
    Dim rct As Long
    rct = Application.WorksheetFunction.CountA(Range("B:B"))
    ActiveCell.FormulaR1C1 = "=SUM(R &[Rct]C:R[-1]C)"
    
    
    End Sub


    :thanx:


    Robert

    Re: For each construct excluding the last two sheets


    Quote from Dave Hawley

    Hi Robert


    Try

    Code
    Sub Duh()
         
        Dim lLoop As Long
         
        For lLoop = 1 To Worksheets.Count - 2
            MsgBox Worksheets(lLoop).Name
        Next lLoop
    End Sub


    Hi Dave,
    This works really good thank you, the problem is that I am trying to run a countif formula in all the sheets except for the last two sheets.
    The code looks like this:


    Do you have any ideas to eliminate the last two sheets from this macro?


    //Robert :confused:

    Hi all,


    I am really stupid I cannot get a macro to exclude the last two sheets in a workbook when I am using a for each construct. I tired with the following and that did not fly:


    Anyone know how to exclude the two last sheets from the for each construct?


    /Robert

    Re: For each construct does not work


    Quote from norie

    The reason this doesn't work is because you only define r once and only for the active worksheet.


    Try defining it within the loop and by referencing the worksheet.


    Thank you Norie ...learnt a new thing today... :eureka: :thanx:

    Hello all,


    I am using a countif formula on a range in all sheets in a workbook (specifically range P2 to P500). If the occurence is >= 2 then I want to colour the background in the cell blue, however I want to do this accross all worksheets in the active workbook. For some reason it does not work, can anyone see what I have done wrong?



    :thanx:
    Robert

    Re: Row Delete by Countif function



    Sorry was a bit to quick to answer...This did the stuff.. :thanx:
    :yourock:


    Robert

    Re: Row Delete by Countif function



    Yes I want to remove the rows where the data under all the dates is 0.


    Hum see what you mean about columns A to C - Do have any clue to get round that?


    Robert

    Re: Row Delete by Countif function


    Quote from norie

    Robert


    What exactly are you trying to do?


    Could you not just sort blank rows to the bottom?


    Hello Norie,


    I think it is better that I send the information I have in the worksheet (removing about 4500 rows but still the basics of what it looks like), hopefully thing will get clearer then:


    Robert

    Re: Row Delete by Countif function


    Quote from youtocc

    What about this?

    Code
    Sub DeleteEmptyRows()
        Dim LastRow As Long, i As Long
        LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
        For i = LastRow To 1 Step -1
            If WorksheetFunction.Sum(Rows(i)) = 0 Then
                Rows(i).EntireRow.Delete
            End If
        Next i
    End Sub


    Yes that would work fine if I did not have Row headings and if I did not have figures with - signs and plus sign (I could get a sum = 0 for those occasions even though the row has data that I need)


    My data looks sort of like this...


    Fig1 Fig2
    Dogs 0 0
    Cats 1 2
    Birds 2 1


    Then the countif formula cannot be applied.


    Robert

    Re: Row Delete by Countif function


    Ok,


    I have enclosed a workbook with the basic jist of what I am trying to do, but in VBA. Ie if the countif value renders a zero then remove the row. Hope you understand what I mean.
    Robert