Results 1 to 1 of 1

Inserting rows such that reference rows is after an automatic pagebreak

Threaded View

  1. #1
    Registered User
    Join Date
    10-04-2013
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1

    Post Inserting rows such that reference rows is after an automatic pagebreak

    Hi
    I am new to VBA and I have the following problem:

    I want to have different sections in a file and would like to start each section on a seperate page. I do not want to use a manual pagebreak since part of the page appears empty.
    I would therefore like to insert blank rows untill the following section starts just after an automatic pagebreak. My code is as follows:
    Sub insertrows()
    Dim pg As Long
    Dim T1 As Long
    Dim T2 As Long
    Dim x, y As Long
    Dim rownum As Long
    Dim i As Integer
    Dim rowinsert As Integer
    Dim SearchRange As Range
    
    
    x = 1
    
    
    With ActiveSheet
        For pg = 1 To .HPageBreaks.Count
        rownum = .HPageBreaks(pg).Location.Row
       
        
        T1 = Application.Match(x, Range("A5:A1000"), 0)
        If IsError(T1) Then
        MsgBox "not found"
        Else
        MsgBox "found at pos: " & FindRowT1
        End If
        
      
        T2 = Application.Match(x + 1, ActiveSheet("A5:A1000"), 0)
         If IsError(T2) Then
            MsgBox "not found"
            Else
            MsgBox "found at pos: " & FindRowT2
            End If
    
    
            If T1 > rownum And T2 < rownum And x < 30 Then
            x = x + 1
            i = 0
            ElseIf T1 < rownum And T2 < rownum And x < 30 Then
                rowinsert = rownum - T2
                For i = 1 To rowinsert
                Rows(T2).Insert
                Next i
            x = x + 1
            'T2 = T2 + 1
            Else
            x = x + 1
            'T2 = T2 + 1
            End If
        Next
    End With
    End Sub
    I have been facing the following problems:

    T1 is able to be found, but T2 is giving the "Object does not support this property method".

    I also tried with it with the following alternative
     T1 = Application.WorksheetFunction.Match(x, Range("A1:A4000"), 0)
        T2 = Application.WorksheetFunction.Match(x + 1, Range("A1:A4000"), 0)
    but it is giving me an error on the second iteration.
    Any suggestions is welcomed.

    A sample file is also attached.
    Niraj
    Attached Files Attached Files
    Last edited by Tester80; 10-10-2013 at 02:04 AM. Reason: Add sample file

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-01-2012, 05:54 AM
  2. using reference with inserting rows
    By excelmanager in forum Excel General
    Replies: 6
    Last Post: 10-28-2011, 05:00 AM
  3. Automatic Formula Fill when Inserting Rows
    By ACS in forum Excel General
    Replies: 2
    Last Post: 08-10-2009, 05:02 PM
  4. Replies: 5
    Last Post: 04-11-2009, 07:37 AM
  5. Inserting Rows via function and reference
    By bigdaddymrp in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-21-2008, 08:43 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1