Range info for use in VBA Macro

John_Mason

New Member
Joined
Jul 27, 2010
Messages
4
Hello,

I want to identify the last row in a spreadsheet that has data.
I have defined an Integer to hold the output from CountA i.e.

Dim NoOfRows As Integer
NoOfRows = Application.WorksheetFunction.CountA("Sheet1!A2:A65535")

When I try to use Range ("A2:NoOfRows").Select I get Run-time error '1004':
Method 'Range' of object '_Global' failed.

I am a novice VBA person so this is probably a school boy error. Any suggestions please.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
There are a couple of different ways to find the last row with data.

In 2003 if you do this
Code:
Dim NoOfRows As Integer
NoOfRows = sheet1.range("A65535").End(xlup).row
it shhould give you the row number of the last row. What you seem to be doing is counting the number of rows, which I suspect might be different.

The reason it fails is because of incorrect syntax, try:
Code:
Range ("A2:A" & NoOfRows).Select
 
Upvote 0
Thanks very much for the response. the macro now steps past that point :). I now get Unable to set the LineStyle property of the border class.

With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
The other With commands work fine.
 
Upvote 0
That code snippet works for me. Can you paste the whole macro?

Code:
Sub test()
Dim NoOfRows As Long
NoOfRows = Sheet1.Range("A65535").End(xlUp).Row
Range("A2:B" & NoOfRows).Select

With Selection.Borders(xlInsideVertical)

    .LineStyle = xlContinuous
End With
End Sub

I did notice one thing that can cause problems. The row number is an integer and will thus fail if the number goes above 32,000 something.
 
Upvote 0
Sub Format_Extract()
'
' Format_Extract Macro
' Macro recorded 26/07/2010 by Mason J
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Dim NoOfRows As Integer
NoOfRows = Application.WorksheetFunction.CountA("Sheet1!A2:A65535")

Columns("A:A").Select
Selection.ColumnWidth = 18

Columns("B:B").Select
Selection.ColumnWidth = 10

Columns("C:C").Select
Selection.ColumnWidth = 18

Columns("D:D").Select
Selection.ColumnWidth = 18

Columns("E:E").Select
Selection.ColumnWidth = 24

Columns("F:F").Select
Selection.ColumnWidth = 40

Columns("G:G").Select
Selection.ColumnWidth = 10

Columns("H:H").Select
Selection.ColumnWidth = 10

Columns("I:I").Select
Selection.ColumnWidth = 20

Columns("J:J").Select
Selection.ColumnWidth = 20

Columns("K:K").Select
Selection.ColumnWidth = 20

Range("A2:A" & NoOfRows).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2:A" & NoOfRows).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
End Sub

There are only 572 rows so I am not sure why I would exceed 32000.
 
Upvote 0
I can't see anything in there that would prevent it running. Especially since the other border changes do work right?
 
Upvote 0
Thank you very much for your help. when I find out why it objects, I will let you know the cure.
Thanks again.
 
Upvote 0
I have one final suggestion to make.

I did not get any problems in 2007 but I do in 2003. If only 1 column is selected, as you have then it will fail because there is no InsideVertical on a single cell I imagine.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top