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?

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


    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?

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



    Re: For each construct excluding the last two sheets

    Quote from Dave Hawley

    Hi Robert


    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?


    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?


    Re: Row Delete by Countif function

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


    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?


    Re: Row Delete by Countif function

    Quote from norie


    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:


    Re: Row Delete by Countif function

    Quote from youtocc

    What about this?

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


    Re: Row Delete by Countif function


    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.