Home>
I have an Excel file that stores about 10,000 lines of data from the top. (Title up to the second line)
When the value (character) in column K (11th line) is changed, a page break is made at the changed location.
I want to create a macro to insert, but if I use the following macro, a page break will be inserted.
It is not done or the reason is unknown and it is in trouble.
there
(1) Please tell me why there are times when line breaks are not inserted in this macro.


② Please tell me if there is a place where you can improve.

Error message There are no
errors. No line breaks
Applicable source code
vba
sub Page break ()
    Dim i As Long
    Dim SaveKey As Variant

    'Delete all page breaks
    Worksheets ("sheet name"). ResetAllPageBreaks
    'Set the first and second lines as the title line
     Application.PrintCommunication = False
    With Worksheets ("Sheet name"). PageSetup
        .PrintTitleRows = "$1: $2"
        .PrintTitleColumns = ""
    End With
    i = 3
    SaveKey = Cells (i, 11) .Value
    'Loop until column K value is blank
    Do Until Len (Cells (i, 11) .Value) = 0
        'Check if the key has changed
        If SaveKey<>Cells (i, 11) .Value Then
            'Insert page break
            Worksheets ("sheet name"). HPageBreaks.Add Before: = Cells (i, 1)
            'Update key
            SaveKey = Cells (i, 11) .Value
        End If
        i = i + 1
    Loop
    'Show print preview
    Worksheets ("sheet name"). PrintPreview
End sub
Supplemental information (FW/tool version etc.)
This is the environment for Windows7 32bit Excel2010.
The sheet name contains the name of the book sheet used for practical use.
vba
  • Answer # 1

    Everyone.

    What you want to do is
    "If you look at the data in column K in order from the top and the value of the cell you are looking at is different from the value of the cell above it, you want to insert a page break." But it is that?

      

    "I want to create a program that breaks when the value changes"

    When you say "When the value changes",
    It is interpreted as the time when the value of the target cell has just changed.
    And is n’t it a line break?
    Excel won't give you the expected answer if you make a mistake in every word.
    For humans, I guess what I want to say by analogy with the context, but ^^;

    I'm confused because it doesn't work, I don't have time, and I'm eager to write,
    You may have used a strange wording, but don't get it wrong,
    If you can't explain, you won't be able to write programs.

    Now, why is it ①? The question is that you can't understand unless you move the same macro with the same data, but you have to be able to find the mistake yourself.
    I think VBE is used as a tool to write VBA, but this tool is
    In order to find mistakes, the program can be run line by line.

    https://asatte.biz/vba-debug-menu/

    If you do this, you may find mistakes.
    (It is unknown whether it can be found in this case)
    In this case,

      

    "No page breaks inserted or not"

    That means

      

    If SaveKey<>Cells (i, 11) .Value Then

    You can imagine that the intended judgment is not made at

    .
    So, while stepping, the value being compared is
    I want to check what the values ​​are and what I want to do,
    I think you should check what happens.
    In particular, the difference between half-width and full-width alphanumeric characters cannot be seen on the screen, so
    It's a good idea to pay attention to that area.
    Other symbolic systems are similar but sometimes different, so
    (For example, is it difficult to understand the difference between Excel and Excel?)
    Let's watch out.
    If you don't understand and want to ask someone else to debug you,
    There is one way to check the operation with the same data as well as the macro code.
    In this case, data in column K?
    I think it would be nice to be calm and confirmed or explained.

    Assuming that you debug your code yourself,
    Let's organize what we want to do again.

    0) Program start
    1) Cancel a page break on the sheet
    2) Look until data disappears from K3
    3) Compare the value of the cell you ’re looking at and the value below (or above)?
    Insert page breaks if they are different
    4) Return to 2
    5) End of program

    I think this will happen.
    Here, Excel's nephew and the necessary code before writing the code,
    Let's explore using a function called "Record Macro".

    Sub Macro1 ()
    '
    'Macro1 Macro
    ''
        Range ("B6"). Select
        ActiveWindow.View = xlPageBreakPreview
        Range ("A1"). Select
        ActiveCell.FormulaR1C1 = "1"
        Range ("A1"). Select
        Selection.AutoFill Destination: = Range ("A1: A37"), Type: = xlFillDefault
        Range ("A1: A37"). Select
        Range ("A9"). Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before: = ActiveCell
        Range ("A21"). Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before: = ActiveCell
        Range ("A30"). Select
        ActiveSheet.ResetAllPageBreaks
    End Sub


    Look at what you have done and the recorded code, and also check the help.
    http://www.ken3.org/vba/excel-help.html

    As a result, a page break is inserted above the specified cell.
    See from K4 and repeat until one cell above is blank
    I will write it.

    '0) Program start
    Sub test ()
        Dim i As Long
        '1) Cancel a page break on a sheet
        ActiveSheet.ResetAllPageBreaks
        'Set initial line number
        i = 4
        '2) Repeat as long as the cell above the target cell is not blank
        Do While Cells (i-1, "K"). Value<>Empty
            With Cells (i, "K")
                '3) If the value of the cell you are looking at is different from the value above it
                If .Value<>.Offset (-1) .Value Then
                    'Insert a page break
                    ActiveSheet.HPageBreaks.Add Before: =. Cells
                End If
            End With
            'Prepare next line number
            i = i + 1
            '4) Return to 2
        Loop
        '5) End of program
    End Sub
      

    ② Please tell me if there is a place where you can improve.

    In the first place,
    Since there is a function to add a page break to the "subtotal" function,
    If you count the number of data with the subtotal function,
    Macros are not necessary and can be processed faster than the code I wrote earlier.
    If you really do not need a formula,
    You can search for the formula that was inserted with the jump function and delete the line.
    If there are other formulas,
    I think you should search for blanks in the data with the jump function.
    If that is n’t enough, search for and replace or filter and find out what you can do.
    I think we should solve it.
    And if the procedure becomes complicated, then automate the operation (= macro)
    I think you should think about it.
    If the procedure is clear, try recording it as a macro.
    Brush up recorded macros for general use
    I think it should be improved.

  • Answer # 2

    Roughly compile the code

    Compare the value in column K from the fourth row until the value is empty from the fourth row down, and change if it is different Page

    The content is

    . I tried moving it with EXCEL at hand, but

      

    Page breaks inserted or not

    status could not be confirmed.I think it works correctly according to the code written.

    And next, "What you want to do" in the question text

      

    When the value (character) in column K (11th line) is changed, a page break occurs at the changed location

    If you want content that can be interpreted as it is read,
    The posting code is not fulfilling the request in all.

    Is it popular to implement usingWorksheet_Changearoundchanged? I think and
    To determine whether the change waschanged, you will need to comparevalue before changewithcurrent value. (The comparison target should not be the value in the upper row.)

    Because we are not able to judge what is correct, please check it yourself.

  • Answer # 3

    What the questioner wants to do is
    "Enter page breaks before and after the character string in column K is switched between the upper and lower cells."
    It seems that.
    In the explanation of the text, it is misunderstood when you type a character in a cell.

    In

    , I don't think there will be any worries when looking at the source.
    Conversely, there is a possibility that a page break will occur at an unintended location.

    The method of presentation distinguishes between "quote numbers" and "numerical numbers", so page breaks occur where the number 1 and the character "1" are consecutive. I will enter.
    If you change all the K columns to the string format, you will tend to think that it is OK. Will occur.

    In light of that, I think that the cell contents should be converted to Strings every time.

    sub page break ()
    Dim i As Long
    Dim SaveKey As String
    'Delete all page breaks
    Worksheets ("sheet name"). ResetAllPageBreaks
    'Set the first and second lines as the title line
    Application.PrintCommunication = False
    With Worksheets ("Sheet name"). PageSetup
    .PrintTitleRows = "$1: $2"
    .PrintTitleColumns = ""
    End With
    i = 3
    SaveKey = CStr (Cells (i, 11) .Value)
    'Loop until column K value is blank
    Do Until Len (CStr (Cells (i, 11) .Value)) = 0
    'Check if the key has changed
    If SaveKey<>CStr (Cells (i, 11) .Value) Then
    'Insert page break
    Worksheets ("sheet name"). HPageBreaks.Add Before: = Cells (i, 1)
    'Update key
    SaveKey = CStr (Cells (i, 11) .Value)
    End If
    i = i + 1
    Loop
    'Show print preview
    Worksheets ("sheet name"). PrintPreview
    End sub


    SaveKey is changed to String type, and cell data is converted to a string with the CStr function.

    In

    , "If no page break is inserted", it cannot be read from the question text.
    If you can provide a concrete example of what data will be inserted, page breaks will not be inserted.
    Please add a concrete example.

Related articles