在Excel VBA中，如何删除灰色分页符？

Question:

How do you remove grey dotted line page breaks (not the blue ones) from an Excel sheet using VBA code?

如何使用VBA代码从Excel工作表中删除灰色虚线分页符(不是蓝色分页符)?

Background:

We have a collection of ODBC spreadsheets generated via VBA. When running two different (though similar) spreadsheets on my local box without manually changing any options in Excel, one shows dotted grey lines on the page breaks (see image below) and one does not. Both are large enough to extend past one page. I have not been able to find a difference in the code that gives reason to this.

我们有一组通过VBA生成的ODBC电子表格。在我的本地方框上运行两个不同(但相似)的电子表格而不手动更改Excel中的任何选项时,一个在分页符上显示虚线灰色线(见下图),而另一个则没有。两者都足够大,可以延伸到一页。我无法找到给出理由的代码中的差异。

Most of the advice on the internet points toward the options in Excel via the GUI, which is not an option. I am seeking a VBA solution.

互联网上的大多数建议都指向通过GUI的Excel中的选项,这不是一个选项。我正在寻求VBA解决方案。

What I've tried:

我尝试过的:

I've tried several different variations and applications of DisplayPageBreaks = False. Either I'm using it incorrectly, or it is not the correct setting. My most reasonable use of this property has been:

我已经尝试了DisplayPageBreaks = False的几个不同变体和应用程序。要么我使用不正确,要么设置不正确。我最合理地使用这个属性是:

ActiveWorkbook.Worksheets(str_Worksheet_Name).DisplayPageBreaks = False

ActiveWorkbook.Worksheets(str_Worksheet_Name).DisplayPageBreaks = False

Note that the variable str_Worksheet_Name works in many other lines in the code, example: ActiveWorkbook.Worksheets(str_Worksheet_Name).Cells(1, 1).Select correctly selects cell A1 on the desired worksheet.

请注意,变量str_Worksheet_Name适用于代码中的许多其他行,例如:ActiveWorkbook.Worksheets(str_Worksheet_Name).Cells(1,1)。选择正确选择所需工作表上的单元格A1。

Reference Image:

These are the lines I am trying to remove:

这些是我想要删除的行:

Thanks in advance.

提前致谢。

Additional Info:

RE Mat's Mug's comment:

RE Mat's Mug的评论:

It sounds like something in this code snippet is preventing them from showing up on this spreadsheet. Note that this snippet is from the sheet that correctly does not show the page breaks:

听起来这段代码中的内容阻止它们显示在此电子表格中。请注意,此代码段来自正确不显示分页符的工作表:

Public Function cmlapi_Set_To_Landscape()

On Error Resume Next
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
End With
If glob_Header_On_Every_Page = True Then
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$" & HEADER_OFFSET
    End With
End If
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1

End Function

Per answer from Mat's Mug:

来自Mat's Mug的答案:

With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .FitToPagesTall = 1
    .FitToPagesWide = 1
End With

No luck with this. The lines are still there. Note that the .Orientation = xlLandscape did work, so there shouldn't be a concern of where this is specified in the code.

没有运气。线路仍在那里。请注意.Orientation = xlLandscape确实有效,所以不应该担心代码中指定了哪个。

Per answer from Vidyata:

来自Vidyata的回答:

ActiveSheet.DisplayPageBreaks works in the immediate window (Ctrl + G) for the spreadsheet after it has been run. Rerunning puts the dotted lines back again. Oddly using this same property in the code doesn't seem to have an effect, but clearly is the correct code. Perhaps there's a specific place it needs to be? I would have thought as long as I either use ActiveSheet when the correct sheet is selected or specified my sheet name it shouldn't really matter.

ActiveSheet.DisplayPageBreaks在电子表格运行后立即在窗口(Ctrl + G)中工作。重新运行再次将虚线放回原位。奇怪地在代码中使用相同的属性似乎没有效果,但显然是正确的代码。也许它需要一个特定的地方?我想,只要我在选择正确的工作表时使用ActiveSheet或指定我的工作表名称就不应该这么重要。

It was further suggested that I try it on the last line. Good idea, but didn't work out either. Tried both of these on the last line in two separate trials:

进一步建议我在最后一行尝试。好主意,但也没有成功。在两个单独的试验的最后一行尝试了这两个方面:

ActiveWorkbook.Worksheets(str_Worksheet_Name).DisplayPageBreaks = False ActiveSheet.DisplayPageBreaks = False

ActiveWorkbook.Worksheets(str_Worksheet_Name).DisplayPageBreaks = False ActiveSheet.DisplayPageBreaks = False

I tried using the PageSetup code above. That didn't do it either.

我尝试使用上面的PageSetup代码。那也没做到。

Solution: See Mat's Mug's answer. FitToPagesTall = 1 and FitToPagesWide = 1 worked after also setting Zoom = False. I'll shamefully admit I'm rather surprised I didn't find this earlier as it is exactly stated as such on Microsoft's Dev Network site:

解决方案:见Mat's Mug的答案。在设置Zoom = False后,FitToPagesTall = 1和FitToPagesWide = 1也有效。我可耻地承认我很惊讶我之前没有发现这一点,因为它在微软的Dev Network网站上就是这样说的:

Link: MSDN Info

链接:MSDN信息

Code:

 .Zoom = False 
 .FitToPagesTall = 1 
 .FitToPagesWide = 1

Not sure why the Zoom line is needed, but that was the missing piece.

不知道为什么需要缩放线,但这是缺失的部分。

Thanks to all who helped!

感谢所有帮助过的人!

3 个解决方案

#1

Try removing any Zoom, and then scaling the PageSetup to fit a single page:

尝试删除任何缩放,然后缩放PageSetup以适合单个页面:

ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesTall = 1
ActiveSheet.PageSetup.FitToPagesWide = 1

That should get the dotted lines out of the way.

那应该是虚线。

#2

The dotted lines are simply showing where the page will break. Has nothing to do with inserted page breaks. So removing page breaks doesn't do anything. The page will still break somewhere unless you specify one page wide/tall per Mats Mug.

虚线只是显示页面中断的位置。与插入的分页符无关。因此,删除分页符不起作用。除非您为每个Mats Mug指定一页宽/高,否则该页面仍会在某处打破。

If you don't want to see the dotted lines, just save, close and reopen. The lines are gone. Of course they will reappear when you preview or print.

如果您不想看到虚线,只需保存,关闭并重新打开。线条消失了。当然,当您预览或打印时,它们会重新出现。

It's a feature, not a flaw.

这是一个功能,而不是一个缺陷。

#3

Write the following in the immediate window and press Enter:

在即时窗口中写下以下内容,然后按Enter键:

ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks

Or simply write Or simpy write - ActiveSheet.DisplayPageBreaks = False in VBA, at the end of your code.

或者简单地在代码末尾写入或简单写入--VBA中的ActiveSheet.DisplayPageBreaks = False。

Worksheet.DisplayPageBreaks Property (Excel)

Worksheet.DisplayPageBreaks属性(Excel)

#1