Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

last row 1

Status
Not open for further replies.
SharonMee

SharonMee

MIS
May 7, 2004
210
0
0
CA
Hi All:

I recorded a macro to higlight rows 5 to 551 and then format it. How do I modify this macro to highlight row 5 to the last row and then do the format?

Thanks for helping me.

SharonMee
Rows("5:551").Select
Range("B5").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
 
Sort by date Sort by votes
Take a look in the FAQ area of this forum.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Sharon,

If your worksheet starts with data in row 5...
Code: 
   With ActiveSheet.UsedRange.EntireRow
      .Borders(xlDiagonalDown).LineStyle = xlNone
      .Borders(xlDiagonalUp).LineStyle = xlNone
      With .Borders(xlEdgeLeft)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Borders(xlEdgeTop)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Borders(xlEdgeRight)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Borders(xlInsideVertical)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
   End With
However, if your data starts in rows 1-4 and you want the formatting to begin in row 5, then change the With statement
Code: 
   With ActiveSheet.UsedRange
      r1 = .Row
      r2 = r1 + .Rows.Count - 1
   End With
   With ActiveSheet.Range(Cells(5, 1), Cells(r2, 1)).EntireRow
:)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
Thanks so much Skip,

I am not getting any errors with the code you provided. But the code to create the all borders is not executing, i.e. my spreadsheet is still not ruled, do you know what's happening?

This part of the code is not working:

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
Thanks again.
 
Upvote 0 Downvote
I dont know -- I cut 'n' pasted and it works.

Please describe the data on your active sheet.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
Hi Skip,
Thanks.
The data on my active sheet has the header in row 5. Usually, row 5 already has the border around it, but the new rows copied and pasted from another sheet, don't have this border, so I want to format row 5 to the last row if there is a last row with the All Border format.
Here is the code I used:

With ActiveSheet.UsedRange
r1 = .Row
r2 = r1 + .Rows.Count - 1
End With
With ActiveSheet.Range(Cells(5, 1), Cells(r2, 1)).EntireRow
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
 
Upvote 0 Downvote
Sharon,

Sometimes, when inserts and deletes have been occurring on a sheet, the UsedRange property does not return the proper value. Put a BREAK in your code just after r2 is assigned and see what the r2 value is -- should be the LAST ROW NUMBER.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
Hi Skip,

I hope you're not running out of patience yet
after r2 in the code you gave me I inserted
[Q4] = r2,

and put a break in the rest of the code, but I am not getting any value.

How can I make it work?
Thanks again.
 
Upvote 0 Downvote
...and you are looking at the Active Sheet? Do you have multiple workbooks open?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
Thanks Skip,
I am looking at the Active Sheet and there is just 1 workbook open.
What else could be the problem?

Thanks again.

 
Upvote 0 Downvote
so what happens when you past and run this...
Code: 
Sub atest()
   With ActiveSheet.UsedRange
      r1 = .Row
      r2 = r1 + .Rows.Count - 1
      ActiveSheet.[q4] = r2
   End With
End Sub
???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
Hi Skip,

I tried the test thing, and yes it gave the the number of the last row, here is the whole code I am running, maybe you can help me find the glitch. I have looked at it (with the little knowledge I have) but can't find anything wrong.

Thanks much again.

Sub UNonAP()
Sheet3.Select
LastRow = Cells(Rows.Count, "c").End(xlUp).Row
If (LastRow >= 6) Then
Rows("6:" & LastRow).Select
Selection.Delete Shift:=xlUp
End If
Sheet5.Select
If ActiveSheet.FilterMode Then
ActiveSheet.AutoFilterMode = False
End If
Dim rng As Range
With Sheet5
On Error Resume Next
.Range("A1").AutoFilter Field:=18, Criteria1:="NO"
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
'set a range = to visible cells (excluding the header)
On Error GoTo 0
If rng Is Nothing Then
Exit Sub
End If
'check whether criteria applied
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
'set a range = to visible cells (excluding the header)
End With
rng.Copy
Sheet3.Range("A6").PasteSpecial
Application.CutCopyMode = False
With ActiveSheet.UsedRange
r1 = .Row
r2 = r1 + .Rows.Count - 1
End With
[q4] = r2
With ActiveSheet.Range(Cells(5, 1), Cells(r2, 1)).EntireRow
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
Sheet5.Select
End Sub
 
Upvote 0 Downvote
Sharon,

[Q4] may be HIDDEN by the AutoFilter.

I ran your procedure and Sheet5 got formatted with borders from row 5 down to the end of data.

Do you have enough "NO" values in column 18
Code: 
    With Sheet5
        On Error Resume Next
        .Range("A1").AutoFilter Field:=18, Criteria1:="NO"
        Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1) _
        .SpecialCells(xlCellTypeVisible)
???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
Yes Skip, I have enough rows. In the sample I am working on, I have 6 "No"s i.e 6 rows.

The code works okay by filtering for the "No" in sheet5 and then copies and pastes these rows in sheet3, so the code is working up to the line 'Application.CutCopyMode = False" but the code does not execute the remainder of the code from here.

I don't know why it's working for you and it's not working for me.

Also Q4 is in sheet 3 and it's not filtered, this sheet is not filtered at all. When I did the test code you gave me, without the remainder code it worked, but when I added the other code, Q4 is not returning the value at all.

Hmmm.. could this be a bug?

Is there another way I could create an All border apart from the way it is in my code presently?

Thanks again.

 
Upvote 0 Downvote
Sheet5 is the activesheet NOT sheet3

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
sharon,

Consider NOT Activating and Selecting excelt where you want to end up displaying to the user after the macro is done. faq707-4105 How Can I Make My Code Run Faster?
Code: 
Sub UNonAP()
   With Sheet3
      LastRow = .Cells(.Rows.Count, "c").End(xlUp).Row
      If (LastRow >= 6) Then
         .Rows("6:" & LastRow).Delete Shift:=xlUp
      End If
   End With
   With Sheet5
      If .FilterMode Then
         .AutoFilterMode = False
      End If
      Dim rng As Range
        On Error Resume Next
        .Range("A1").AutoFilter Field:=18, Criteria1:="NO"
        Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1) _
        .SpecialCells(xlCellTypeVisible)
         'set a range = to visible cells (excluding the header)
        On Error GoTo 0
      If rng Is Nothing Then Exit Sub
     'check whether criteria applied
      Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1) _
      .SpecialCells(xlCellTypeVisible)
     'set a range = to visible cells (excluding the header)
      rng.Copy
      Sheet3.Range("A6").PasteSpecial
      Application.CutCopyMode = False
      With .UsedRange
        r1 = .Row
        r2 = r1 + .Rows.Count - 1
      End With
    [q4] = r2
       With .Range(Cells(5, 1), Cells(r2, 1)).EntireRow
           .Borders(xlDiagonalDown).LineStyle = xlNone
           .Borders(xlDiagonalUp).LineStyle = xlNone
            With .Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
      End With
      .Activate
   End With
End Sub


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
Just from viewing this post as new posts are written, I've noticed something...

couldn't you do the following to cut down on lines of code:

change
Code: 
        With .Range(Cells(5, 1), Cells(r2, 1)).EntireRow
           .Borders(xlDiagonalDown).LineStyle = xlNone
           .Borders(xlDiagonalUp).LineStyle = xlNone
            With .Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
      End With
to
Code: 
    With .Range(Cells(5, 1), Cells(r2, 1)).EntireRow
       .Borders(xlDiagonalDown).LineStyle = xlNone
       .Borders(xlDiagonalUp).LineStyle = xlNone
        For i = xlEdgeLeft to xlInsideHorizontal Step 1
            With .Borders(i)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
        Next i
    End With

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?
 
Upvote 0 Downvote
Thanks a lot guys. I didn't know the select, activate thing could slow down the macro, thanks for pointing that out to me Skip.
CLFlava, thanks for making the macro shorter.
Thanks for all your help.

Sharon
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
Back coloring an entire row if a duplicate is found in a dynamic range 1
Replies
18
Views
220
SkipVought
S
MattGreer
  • Locked
  • Question
Delete a Table Row in a Filtered Table 2
Replies
11
Views
250
SkipVought
S
remeng
  • Locked
  • Question
Row count for table after filtering
Replies
8
Views
381
remeng
remeng
remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
132
remeng
remeng
corbinap
  • Locked
  • Question
Find verbiage and transpose values between each row with the verbiage
Replies
11
Views
86
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top