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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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
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
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,974
Messages
5,508,513
Members
408,688
Latest member
Bhojraj

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top