General discussion

Locked

Excel VBA problem

By M.R.Chambers ·
I have an excel spreadsheet with an unknown number of rows (the number changes as the sheet is used). I want a macro that will determine how many rows are being used and will select only those rows. I know the starting row, and I can get the numberof rows being used easily with the CountA() function, but I keep getting an Type Missmatch error when I use
Rows.(A5:rnge).Select
or any other similar conbination I could think of. Is there any way around this problem?

Thanks
M.R.Chambers

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel VBA problem

by keith.hall1 In reply to Excel VBA problem

Public Sub selectUsed()
Dim n As Integer
Dim ws As Worksheet

Set ws = Application.ActiveSheet
n = ws.UsedRange.Rows.Count
Rows("1:" & CStr(n)).Select
Selection.Copy
End Sub

Regards

Keith Hall

Collapse -

Excel VBA problem

by M.R.Chambers In reply to Excel VBA problem

Just a few slight modifications, and it worked perfectly. Thanks

Collapse -

Excel VBA problem

by csanosi In reply to Excel VBA problem

Here is what I usually use...

Dim strRow As String
Dim intRow As Integer

intRow = ActiveCell.SpecialCells(xlLastCell).Row
strRow = "1:" & Trim(Str(intRow))
Rows(strRow).Select
Selection.Copy

Collapse -

Excel VBA problem

by M.R.Chambers In reply to Excel VBA problem

Thanks, it worked, but so did the first answer, hence no points. Sorry

Collapse -

Excel VBA problem

by RuudA In reply to Excel VBA problem

There are a number of ways to count the rows that are "used' in a spreadsheet.
1) assumption there are no empty rows, you could look for the current region, this will give you a range as result. from there you can get the Row count... example:
DimMyRange as Excel.Range
Dim iRow as double Dim iRows as double
set MyRange=Activesheet.Cells(5,1).CurrentRegion
'iRows then will give you the number of rows in this Range....
iRows=MyRange.Rows
If after this you want to select this, just do:
Rows("5:" & iRows).select (or .Copy) or whatever you need.
2) on the assumption that there are empty rows in the range, just goto the last row in the sheet (ie: row= 65536 in excel97)
Activesheet.cells(65536,1).select
'and work your way up to the first row with anything in the first cell of that row...
activecell.end(xlup).row 'this will give you the row number of the LAST row with data...
so use this as follows:
iRows=activesheet.cells(65536,1).end(xlup).row
then set MyRange=range("5:" & iRows)
MyRange.copy (or MyRange.select)

Collapse -

Excel VBA problem

by M.R.Chambers In reply to Excel VBA problem

Sorry, too late. Already got it working

Collapse -

Excel VBA problem

by M.R.Chambers In reply to Excel VBA problem

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums