Longest Column not finding last couple of rows

[Rackette]

Good morning/afternoon.
If someone has some time to help, I would appreciate it.

So, I've been using this code to find my longest row/column range so I can put 'all borders' around each cell in that range. However, on this last report, it doesn't seem to see the last two rows. Not sure why. My header row is on row 5 and there is something in every cell in column A and there are 10 columns.

I found this two lines, that sound like they should/might work, but I don't know how to substitute them in.
UsedCol = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(x1CellTypeLastCell).Column
UsedRow = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(x1CellTypeLastCell).Row


This is what works most of the time but won't see the last two rows of my current data range.

Sub AllBordersEvenBlanks5()


Application.ScreenUpdating = False
Dim lngLstCol As Long, lngLstRow As Long

lngLstRow = ActiveSheet.UsedRange.Rows.Count
lngLstCol = ActiveSheet.UsedRange.Columns.Count
For Each rngCell In Range("A5:A" & lngLstRow)
    If rngCell.Value > "" Then
        r = rngCell.Row
        C = rngCell.Column
        Range(Cells(r, C), Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
    End If
Next

End Sub

 

[Joe4]

Does it work if you put this line at the beginning of your code?

ActiveWorkbook.Save

I think if data is added after the last save and before the code is run, the Used Range may not be updated until the file is saved again.
So saving it at the beginning of your code may fix your issue.

 

[Fluff]

If you want to put a border on every cell in the usedrange try

Sub Rackette()
   ActiveSheet.UsedRange.Borders.Weight = xlThin
End Sub

 

[MARK858]

Just to point out a few things....

lngLstRow = ActiveSheet.UsedRange.Rows.Count

Counts the number of rows in the usedrange so unless your data started in row 1 you would get an incorrect number in

Range("A5:A" & lngLstRow)

For instance in the below

Book1
	A	B	C
1
2
3	aaaa	aaaa	aaaa
4	aaaa	aaaa	aaaa
5	aaaa	aaaa	aaaa
6
7
8
9
Sheet1

The usedrange is A3:C5 so there are only 3 rows in the usedrange. This means if you done Range("A1:A" & ActiveSheet.UsedRange.Rows.Count) it would only include A1:A3.
Which was probably your issue.


In the copy of sample lines you posted the red 1 should be a lowercase L.

UsedCol = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(x[COLOR="#FF0000"]1[/COLOR]CellTypeLastCell).Column
UsedRow = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(x[COLOR="#FF0000"]1[/COLOR]CellTypeLastCell).Row

Finally if memory retention in an issue with the usedrange then you could try

Sub GetRange()
	Dim LstRw As Long, LstCo As Long
	LstRw = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
	LstCo = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
	Range(Cells(5, "A"), Cells(LstRw, LstCo)).Borders.Weight = xlThin
End Sub

 

[Leith Ross]

As an addendum to Mark858's post, the UsedRange includes formatted cells empty or not.

 

[MARK858]

As an addendum to Mark858's post, the UsedRange includes formatted cells empty or not.

Another reason why I rarely use it


I should have also stated that the code I posted ignores cells with formulas that return "" as a result, if you wanted them included you would use...

Sub GetRange()
	Dim LstRw As Long, LstCo As Long
	LstRw = Cells.Find("*", , [COLOR="#FF0000"]xlFormulas[/COLOR], , xlByRows, xlPrevious).Row
	LstCo = Cells.Find("*", , [COLOR="#FF0000"]xlFormulas[/COLOR], , xlByColumns, xlPrevious).Column
	Range(Cells(5, "A"), Cells(LstRw, LstCo)).Borders.Weight = xlThin
End Sub

 

[Rackette]

Thank you to all of you for taking the time to help address my issue.
Mark858s code that includes would have a "" formula result, seems to have fixed the problem.
It amazes me how much I have learned from the people who have asked the questions on this forum and from the people who have taken time to respond and answer those questions.
It is very appreciated!
-Christine

 

[MARK858]

You're welcome, thanks for the feedback