Consulting

Results 1 to 7 of 7

Thread: Add border top and bottom row for all pages

  1. #1
    VBAX Regular
    Joined
    Jun 2019
    Posts
    59
    Location

    Add border top and bottom row for all pages

    Hi,

    I have excel file which is the list of details entered.

    I write the code for automatically do the page brake and bordering to top and bottom row of all the pages .

    My code is

    Sub Calibration_Certificate_Printout()




    'For page breake setup


    Dim ws As Worksheet, Rg As Range, LastRow1 As Long, Count As Long ', ii As Long


    ii = 44 ' first page break
    Set ws = ActiveSheet


    With ws
    LastRow1 = .Range("B" & .rows.Count).End(xlUp).Row
    Count = LastRow1
    Set Rg = .Range("B4", "G" & Count) 'The range of the document


    If LastRow1 > 30 Then ' count is the number of row. Break at every 15 rows
    .ResetAllPageBreaks
    .PageSetup.PrintArea = Rg.Address


    While Count > 0 And ii < LastRow1
    If Count > 40 Then ' no page break if there is less than 15 rows left
    '.Rows(ii).PageBreak = xlPageBreakManual
    .HPageBreaks.Add Before:=.rows(ii)

    End If
    ii = ii + 40
    Count = Count - 40
    Wend
    End If


    'For alignment


    Dim i As Long
    For i = 1 To ActiveSheet.HPageBreaks.Count
    With Range(ActiveSheet.HPageBreaks(i).Location.Address).Offset(-1).Resize(, 7).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With

    Next i
    End With
    End Sub




    with this code, while taking print the border is not updating for first row of all pages.


    Can any one please help to this

    The file is attached here
    Attached Files Attached Files

  2. #2
    VBAX Expert
    Joined
    Apr 2005
    Posts
    758
    Location
    Could you please put your code between code tags.
    Makes it a lot more pleasant to read.
    Highlight your code and click on the # at the top of the window.

  3. #3
    VBAX Regular
    Joined
    Jun 2019
    Posts
    59
    Location
    My code is

    #################################################
    Sub Calibration_Certificate_Printout()
    'For page breake setup

    Dim ws As Worksheet, Rg As Range, LastRow1 As Long, Count As Long ', ii As Long
    ii = 44 ' first page break
    Set ws = ActiveSheet
    With ws
    LastRow1 = .Range("B" & .rows.Count).End(xlUp).Row
    Count = LastRow1
    Set Rg = .Range("B4", "G" & Count) 'The range of the document
    If LastRow1 > 30 Then ' count is the number of row. Break at every 15 rows
    .ResetAllPageBreaks
    .PageSetup.PrintArea = Rg.Address
    While Count > 0 And ii < LastRow1
    If Count > 40 Then ' no page break if there is less than 15 rows left
    '.Rows(ii).PageBreak = xlPageBreakManual
    .HPageBreaks.Add Before:=.rows(ii)
    End If
    ii = ii + 40
    Count = Count - 40
    Wend
    End If


    'For alignment
    Dim i As Long
    For i = 1 To ActiveSheet.HPageBreaks.Count
    With Range(ActiveSheet.HPageBreaks(i).Location.Address).Offset(-1).Resize(, 7).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    Next i
    End With
    End Sub
    #################################################
    Last edited by elsuji; 09-05-2019 at 05:08 AM.

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,889
    Location
    The tags are inserted by clicking the # icon on the reply toolbar. OR, type them: (code)your code here(/code) but replace ()'s with []'s.

  5. #5
    VBAX Regular
    Joined
    Jun 2019
    Posts
    59
    Location
    I am not clear on ur reply. Can you explain please

  6. #6
    VBAX Regular
    Joined
    Jun 2019
    Posts
    59
    Location
    Sub Calibration_Certificate_Printout()
    'For page breake setup
    
    Dim ws As Worksheet, Rg As Range, LastRow1 As Long, Count As Long ', ii As Long
    ii = 44 ' first page break
    Set ws = ActiveSheet
    With ws
    LastRow1 = .Range("B" & .rows.Count).End(xlUp).Row
    Count = LastRow1
    Set Rg = .Range("B4", "G" & Count) 'The range of the document
    If LastRow1 > 30 Then ' count is the number of row. Break at every 15 rows
    .ResetAllPageBreaks
    .PageSetup.PrintArea = Rg.Address
    While Count > 0 And ii < LastRow1
    If Count > 40 Then ' no page break if there is less than 15 rows left
    '.Rows(ii).PageBreak = xlPageBreakManual
    .HPageBreaks.Add Before:=.rows(ii)
    End If
    ii = ii + 40
    Count = Count - 40
    Wend
    End If
    
    
    'For alignment
    Dim i As Long
    For i = 1 To ActiveSheet.HPageBreaks.Count
    With Range(ActiveSheet.HPageBreaks(i).Location.Address).Offset(-1).Resize(, 7).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    Next i
    End With
    End Sub

  7. #7
    VBAX Regular
    Joined
    Jun 2019
    Posts
    59
    Location
    Hi can you pls help me how to do this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •