VBA in Excel (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

(Edited by gwhitfield on 24-Sep-02 20:02. Hyperlinks added)

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/%5B/url%5D
http://www.cpearson.com/excel.htm%5B/url%5D
http://j-walk.com/ss/%5B/url%5D

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

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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:

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

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

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

Reply | Quote

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

Reply | Quote

Based on this 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

Reply | Quote

I would suggest John Walkenbach’s books.

See http://www.j-walk.com/ss/about/index.htm%5B/url%5D.

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

Reply | Quote