Posts by jproffer

  • Visual Basic - Pick up a range based on a letter within a cell

    Re: Visual Basic - Pick up a range based on a letter within a cell


    For my 2 cells I used J10, and K10. You can change them to whatever you need.


    Code
    Sub test()
Dim MyStart As String
Dim MyFinish As String
    MyStart = Sheets("Sheet1").Range("J10").Value
    MyFinish = Sheets("Sheet1").Range("K10").Value
    
    Range(MyStart & "1:" & MyFinish & "1").EntireColumn.Hidden = True
End Sub



    and of course one to reset everything for easy testing :)

    Code
    Sub Reset()
    Sheets("Sheet1").Columns.Hidden = False
End Sub

  • Tidy up beginners code

    Re: Tidy up beginners code


    I suppose the "remove format" button will take care of that in the future? Highlight offending section, click button....all is well. Is that right?

  • Tidy up beginners code

    Re: Tidy up beginners code


    Yea I meant to mention that too. There is very rarely a reason to select anything. If you see something like:


    Code
    Range("A1").Select
Selection.Rows.AutoFit


    you can combine them (as I said, generally...see comments below) into:


    Code
    Range("A1").Rows.AutoFit


    I only left it in your case because of the loop that comes after.


    Code
    For Each Rng In Selection.Cells 
        Debug.Print Left(Rng, 5); Rng.RowHeight 
        Rng.RowHeight = Application.WorksheetFunction.Max(Rng.RowHeight, 40) 
    Next Rng


    without a selection, there is no "each Rng" in that selection.


    I would probably rewrite that though, to remove the WS function...something like:


    Code
    For Each Rng In Selection.Cells 
       If Rng.RowHeight < 40 Then Rng.RowHeight = 40 
Next Rng

  • Tidy up beginners code

    Re: Tidy up beginners code



    See comments added and play around with various ways of doing things. A lot of variables/constants for no reason, IMO.

  • Activate code only when a button is pushed

    Re: I have code - Need it to activate only when a button is pushed


    I missed the "ME"s the first time through. If you take those out, it will act on whatever sheet is active, or you can qualify the sheet you want to act on like:


    Sheets("Name").Cells(.Row, 1) ' Not "A"


    or


    ActiveSheet.Cells(.Row, 1)

  • Activate code only when a button is pushed

    Re: I have code - Need it to activate only when a button is pushed


    Try this in a standard code module. Then create a button and assign it.


  • VBA loop

    Re: VBA HELP IN LOOPs


    OK, here's my first question. What is "iRow" ? You mean the row defined by the variable "i" in the loop? Or is that a(n) (undeclared :) ) variable?

  • VBA loop

    Re: VBA HELP IN LOOPs


    It would probably help someone to help if you post the entire code. In the section you show, there are a lot of variables that are not established/defined.


    Also, if you indent and use code tags that helps too. Code tags are these, but without spaces:


    [ code ]
    your code here
    [ / code ]


    and shows up like this:


  • Preserve cell contents in VBA

    Re: Preserve cell contents in VBA


    Quote

    If value in Cells(2,1) contains value "zarządzanie" (a polish word) value of "zarzadzanie" is returned to MyString


    That's not what you want to happen? What do you want the variable to return? If you say MyString = that cell, then MyString will equal the contents of that cell.


    Maybe I'm missing something...??

  • Make it VBA Short

    Re: Make it VBA Short


    Workbooks don't have ranges...workSHEETS have ranges :)


    You'll have to qualify your worksheet.


    I would also move the ".Open........" down to the next line.

  • Copy every 6th and 7th row

    Re: copy zero every 6th and 7th row


    Try this out:


    Code
    Sub test()
Dim i As Long
    For i = 395 To 1000 Step 7 ' change top (1000 here) as needed
        Range("A" & i).Resize(2).Value = 0
    Next i
End Sub

  • copying row height

    Re: copying row height


    In '07 I could make it work with PasteSpecial, but only if I highlighted all the rows I wanted to set.


    If you get beyond 1000 or so, I would imagine it would be a pretty slow process.

  • copying row height

    Re: copying row height


    If you select all the rows by dragging through the row labels (far left), and copy...when you do a simple Paste it will copy the row heights. Same with column widths. But, naturally, you will have to delete all the data you didn't want to copy.


    or


    It will work with PasteSpecial > Formats, but you have to select the same number of rows (by labels...far left again) you're copying before you do the PasteSpecial.