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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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
 

John_Mason

New Member
Joined
Jul 27, 2010
Messages
4
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.
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123

ADVERTISEMENT

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.
 

John_Mason

New Member
Joined
Jul 27, 2010
Messages
4
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.
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123

ADVERTISEMENT

I can't see anything in there that would prevent it running. Especially since the other border changes do work right?
 

John_Mason

New Member
Joined
Jul 27, 2010
Messages
4
Thank you very much for your help. when I find out why it objects, I will let you know the cure.
Thanks again.
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,158
Messages
5,657,160
Members
418,363
Latest member
Debating_Earth

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
Top