Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Ventura, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA in Excel (Excel 2002)

    Okay guys need some help. I have an excel SS that has 3 different macros written prior to me coming on board. One of the issues I am trying to resolve with these macros is that it requires the user to count the number of rows on worksheet and edit the macro with the number of rows. I cannot tell you how many times they have changed other things as well and it is a nightmare. I want to know if there is a way to count the number of rows on the worksheet and pull it back into the macro as a variable.

    Also I am not that proficient in VBA and was wondering if you folks could point in the correct direction of books or web sites.


    Thanks,

    Dan

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA in Excel (Excel 2002)

    Using UsedRange.Rows can be a problem if there are empty rows at the top of the sheet, or if rows at the bottom have been deleted and the workbook has not been saved since the delete.

    If you know that a cell in a column (lets say column A for the code below) always has something in the last row, then the following code will find the last row:

    <pre>Dim lLastRow As Long
    lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    </pre>


    If you don't know which column has the last row entry, then you can use the code below to find the last row:

    <pre>Dim lLastRow As Long, I As Integer
    lLastRow = 0
    For I = 0 To 255
    If Worksheets("Sheet1").Range("A65536").Offset(0, I).End(xlUp).Row > lLastRow Then
    lLastRow = Range("A65536").Offset(0, I).End(xlUp).Row
    End If
    Next I
    </pre>

    Legare Coleman

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: VBA in Excel (Excel 2002)

    <P ID="edit" class=small>(Edited by gwhitfield on 24-Sep-02 20:02. Hyperlinks added)</P>Hi Dan,

    You can quickly get a count of the number of used rows with:
    ActiveSheet.UsedRange.Rows
    So, if you use a statement like:
    Set UsedRows = ActiveSheet.UsedRange.Rows
    you can replace the manually counted value with the 'UsedRows' parameter.

    For web sites with lots of useful links and/or help, try starting at any of:
    http://www.mvps.org/
    http://www.cpearson.com/excel.htm
    http://j-walk.com/ss/

    John Walkenbach (owner of the last of the above) has produced some highly regarded books on Excel formulae & VBA programming.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA in Excel (Excel 2002)

    I would suggest John Walkenbach's books.

    See http://www.j-walk.com/ss/about/index.htm.

    Excel 2002 Power Programming with VBA (ISBN: 0764547992)
    Excel 2002 Formulas (ISBN: 076454800X)

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    Ventura, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA in Excel (Excel 2002)

    I want to thank you both for your help. I also purchased John's book. So far it has been helpful. This is what I pieced together:
    Worksheets("GLLINK").Range("A1000").Activate
    ActiveSheet.UsedRange.Select
    MsgBox Selection.Count / 5

    For x = 1 To Selection.Count / 5

    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "100"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[16],2,8)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[15],11,5)"
    ActiveCell.Offset(0, 10).Range("A1").Select
    ActiveCell.FormulaR1C1 = "GL"
    ActiveCell.Offset(0, 3).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[1],4)&LEFT(RC[1],4)"
    ActiveCell.Offset(0, -6).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[10]=""D"",RC[9],""-""&RC[9])"
    ActiveCell.Offset(1, -11).Range("A1").Select

    And it works just fine. The only issue I have now I have to remove certain rows before I can move this data. The first column is my accounting unit...is there a way to to have the macro delete the rows where a cell in Coloumn begins with 160.xxxxx or what have you?? I have it deleting empty rows and moving them up but I have had no luck in having it Identifying a variable number in column A and removed the whole row like a blank row.

    Any ideas??

    Dan

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA in Excel (Excel 2002)

    Based on <!post=this,180759>this<!/post> neat post from Jan Karel, try something like this:

    Sub DeleteCriteriaRows()
    ' change "A1:" to the appropriate header cell for your data
    Range("A1:A" & ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row).AutoFilter _
    Field:=1, Criteria1:="160.xxxx"
    ' change "2" to the row number under the header cell for your data
    Rows("2:" & ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row).Delete Shift:=xlUp
    ActiveSheet.AutoFilterMode = False
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •