Posts by Foxxy1

  • Add Formula to Bottom of Column

    Alright, below is the code I'm using. If "C1" is the only cell in column "C" that has info the code freaks out and calulates all the way to the bottom (at least I think so). Does anyone know how to avoid this? Also, column "C" will obviously have different lentghs of data, it won't always just be one. Do I essentially need to have a header of some sort to always have more than one cell with a value in it? What if I don't have any values, other than the header, do I then have to write code to bypass it all together? Sorry if the is too many "ifs".

    Code
    Dim cLen2 As Long
cLen2 = Range("C1").End(xlDown).Row
Range("D1").FormulaR1C1 = "=SUMIF(C[-3],RC[-1],C[-2])"
Range("D1", Cells(cLen2, 4)).Formula = Range("D1").Formula

  • Extra Row Included At Bottom

    I've got the following code for the attached worksheet. My problem lies at the end of the code where I'm trying to determine the length of characters in column A. I get a zero value after the last row with an actual value. Does anyone know what is causing this? I know I can change my code to:

    Code
    aLen = Range("A1").End(xlDown).Row - 1


    but that bypasses the problem, rather than fix it.


    By the way, if you run the code, make sure you only have the worksheet "1" in the workbook.


  • Compile 2 Lists On 2 Pages Into 1

    Re: Compile 2 Lists On 2 Pages Into 1


    Well the code above takes 3 colums from one worksheet and makes them one column on the same sheet. What I need should be more clear from the example below.


    Sheet 1
    A1 B1 C1 D1
    A2 B2 C2 D2
    A3 B3 C3 D3


    Sheet 2
    A1 B1 C1 D1
    A2 B2 C2 D2
    A3 B3 C3 D3


    Now those two will be compiled into one
    Sheet 3
    A1 B1 C1 D1
    A2 B2 C2 D2
    A3 B3 C3 D3
    A1 B1 C1 D1
    A2 B2 C2 D2
    A3 B3 C3 D3


    Does that make sense?

  • Compile 2 Lists On 2 Pages Into 1

    Here's some code that I got from an old thread.


    I'd like to do something similar, yet different. I have 4 columns on 2 pages that I'd like to combine this time around. I'd like to end up with this as my result.


    Sheet1ColumnA Sheet1ColumnB Sheet1ColumnC Sheet1ColumnD
    Sheet2ColumnA Sheet2ColumnB Sheet2ColumnC Sheet2ColumnD


    Thanks in advance.

  • Delete Row If Condition Not Met

    Here's my code:

    Code
    Sub DeleteRows()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For i = ws.Range("A65536").End(xlUp).Row To 2 Step -1
        If ws.Cells(i, 9) <> "TRENCH_TYPE" Then
            ws.Rows(i).Delete
        End If
    Next
End Sub


    Attached is my worksheet. I want to remove everything but the header row & rows that have "Trench_Type" in column A. However it's deleting everything but my header.

  • Delete Worksheet If It Exists

    Re: Delete Worksheet If It Exists


    Hmmm, the code break didn't work unless I did it wrong, but I don't believe I did. Also, I just want to verify that the fact that the code is in my personal workbook wouldn't effect anything, right?


    Nevermind, got it (the break) to work. And I just realized that the fact that it's in the personal workbook is exactlly why it's not working. Sorry it took me so long to think about that. Guess I was dropped on my head as a child.

  • Delete Worksheet If It Exists

    Re: Delete Worksheet If It Exists


    No, that works the first time you do it. The reason that is bugging is because the next time you run the code, the sheet doesn't get deleted so it can't make a sheet called cleaned because it is already there.

  • Delete Worksheet If It Exists

    Re: Delete Worksheet If It Exists


    That's weird, I tested the attachment & I bugged on me.
    As far as clearing the sheet, I might have to do that, but at the same time, there should be a way to do this.