printmedia
asked on
Add borders using Excel VBA
Hi all.
How can I add borders to every row or cell starting in the A3 cell and ending in column J using VBA? I will not know how many rows there are because that changes everytime.
Below is the code I use to format some of my columns. I would add it to that code.
Thank you in advance!
How can I add borders to every row or cell starting in the A3 cell and ending in column J using VBA? I will not know how many rows there are because that changes everytime.
Below is the code I use to format some of my columns. I would add it to that code.
Thank you in advance!
Dim rng2 As Long
With Worksheets("Sheet1")
rng2 = .Range("A" & Rows.Count).End(xlUp).Row
.Range("I3:I" & rng2).NumberFormat = "$ 0.0000"
.Range("D3:D" & rng2).Columns.AutoFit
.Range("J3:J" & rng2).Columns.AutoFit
.Range("K3:K" & rng2).Columns.AutoFit
.Range("L3:L" & rng2).Columns.AutoFit
Set MR = Range("J3:J" & rng2)
For Each cell In MR
If UCase(cell.Value) = "SHEET" Then cell.Offset(, -9).Resize(, 12).Interior.ColorIndex = 36
Next
End With
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rob - I think your approach is pretty good too. Tbh almost everything I do I do because I have seen someone cleverer than me do it. The only thing I have to do is as least even vaguely recall that I've seen it somewhere and then find it or rustle it up from the back of my memory!
Hi Stephen,
I work along the same lines & now I can add your trick to my repertoire ;-)
I work along the same lines & now I can add your trick to my repertoire ;-)
I didn't even realise that we could use update the whole of the borders collection in one go. You'll be able to have a chuckle when you glance at my code (unedited) where I loop through the borders - I really don't know why your approach didn't occur to me???
Printmedia,
I was too slow to give an answer to your specific question because I decided to offer you an alternative to your current cell by cell looping at the same time. Anyway, here's what I put together which makes use of excel's native Autofilter functionality. This is usually faster than cell by cell looping.
Open in new window
hth
Rob