remove a text row in Excel rpt before the pagebreak with [Placehol

remove a text row in Excel rpt before the pagebreak with [Placehol

Post by SmFuaX » Sun, 20 Aug 2006 03:41:02

have a placeholder value that prints a row of text across the columns on
every spreadsheet for each department. I want to comment out the code so
it deletes the row at the end of every sheet for ea dept without harming the
procedure that prints an Excel repport. I found 3 instances of
[placeholder] in my procedure. Here are the 3 references in order found.
Can you tell me what lines to comment out or should I comment out all of the
The #3 macro is the one where the text is created I want removed. I think
#1 macro is where it the text is written in to the report.

#2 macro looks like it breaks each spreadsheet into divisions or departments
and then the text is inserted at the end before the break?

I just don't want that text on any page before the break.I'm thinking this
should be really easy.


lrow = lrow + 1
.Cells(lrow, 1).Value = "[placeholder]"

.Cells.ColumnWidth = 20
.Cells.VerticalAlignment = xlTop
.Columns(8).NumberFormat = "m/d"
.Columns(17).ColumnWidth = 3.5


'put a pagebreak at end of a division or department
If sPrevDivision <> "" Then
.Rows(c.Row - 1).Insert Shift:=xlDown
.Cells(c.Row - 2, 1).Value = "[placeholder]"
.HPageBreaks.Add before:=.Cells(c.Row - 1, 1)
lrow = lrow + 1
End If
sPrevDivision = c.Value
sPrevLocation = c.Offset(0, 3)
.Cells(c.Row - 1, 1).Value = sPrevLocation
With .Cells(c.Row - 1, 1).Resize(columnsize:=25)
Call CreateBorder(.Cells, False)
.Interior.ColorIndex = 15
.Font.Size = 14
.Font.Bold = True
End With
End If

For Each c In .Range(.Cells(4, 1), .Cells(lrow, 1))
If c.Value = "[placeholder]" Then
c.Value = "SIGNAGE LOCATION CODES: B1, B2, B3-Booth Set
BD-Back Dom Wall BND-Back Non Dom Wall DF-Double Flat DS-Double Step
F-Feature FD-Front Dom Wall " & Chr(10) & "FDN-Front Non Dom Wall G-Grid
JR-Jet Rail MA-Main Aisle MD-On Clearance NA-Inventory Not Availible S1,
S2-Shop 1, Shop 2 SF-Single Flag TR-Trolley TS-T Stand X-Don't Worry!"
With .Cells(c.Row, 1).Resize(columnsize:=25)
Call CreateBorder(.Cells, False)
.Font.Size = 10
.HorizontalAlignment = xlCenterAcrossSelection
End With
c.Characters(Start:=1, Length:=35).Font.Bold = True
c.Characters(Start:=46, Length:=2).Font.Bold = True
c.Characters(Start:=63, Length:=3).Font.Bold = True
c.Characters(Start:=85, Length:=2).Font.Bold = True
c.Characters(Start:=100, Length:=2).Font.Bold = True
c.Characters(Start:=115, Length:=1).Font.Bold = True
c.Characters(Start:=125, Length:=2).Font.Bold = True
c.Characters(Start:=144, Length:=3).Font.Bold = True
c.Characters(Start:=167, Length:=1).Font.Bold = True
c.Characters(Start:=174, Length:=2).Font.Bold = True

remove a text row in Excel rpt before the pagebreak with [Placehol

Post by Steve Scha » Sun, 20 Aug 2006 04:08:12


The focus of this newsgroup is Macros, in Access, the database program.
You will have a better chance of a good answer in an Excel-related forum.

Steve Schapel, Microsoft Access MVP

Janis wrote: