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

G

Guest

I 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
lines?
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.

-------
rsData.MoveNext
Wend

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

.Cells.ColumnWidth = 20
.Cells.EntireColumn.AutoFit
.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
Next

---
------
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
c.Characters(Start:=186, Length:=2).Font.Bold = True
c.Characters(Start:=200, Length:=2).Font.Bold = True
c.Characters(Start:=216, Length:=2).Font.Bold = True
c.Characters(Start:=243, Length:=6).Font.Bold = True
c.Characters(Start:=265, Length:=2).Font.Bold = True
c.Characters(Start:=280, Length:=2).Font.Bold = True
c.Characters(Start:=291, Length:=2).Font.Bold = True
c.Characters(Start:=302, Length:=1).Font.Bold = True
End If
Next



THANKS!
 
S

Steve Schapel

Janis,

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
I 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
lines?
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.

-------
rsData.MoveNext
Wend

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

.Cells.ColumnWidth = 20
.Cells.EntireColumn.AutoFit
.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
Next

---
------
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
c.Characters(Start:=186, Length:=2).Font.Bold = True
c.Characters(Start:=200, Length:=2).Font.Bold = True
c.Characters(Start:=216, Length:=2).Font.Bold = True
c.Characters(Start:=243, Length:=6).Font.Bold = True
c.Characters(Start:=265, Length:=2).Font.Bold = True
c.Characters(Start:=280, Length:=2).Font.Bold = True
c.Characters(Start:=291, Length:=2).Font.Bold = True
c.Characters(Start:=302, Length:=1).Font.Bold = True
End If
Next



THANKS!
Click to expand...
 
G

Guest

THIS IS A ACCESS MACRO all three of them. It creates a Excel report in
Access with Access data. I really need help getting the text line out.

Steve Schapel said:
Janis,

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
I 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
lines?
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.

-------
rsData.MoveNext
Wend

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

.Cells.ColumnWidth = 20
.Cells.EntireColumn.AutoFit
.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
Next

---
------
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
c.Characters(Start:=186, Length:=2).Font.Bold = True
c.Characters(Start:=200, Length:=2).Font.Bold = True
c.Characters(Start:=216, Length:=2).Font.Bold = True
c.Characters(Start:=243, Length:=6).Font.Bold = True
c.Characters(Start:=265, Length:=2).Font.Bold = True
c.Characters(Start:=280, Length:=2).Font.Bold = True
c.Characters(Start:=291, Length:=2).Font.Bold = True
c.Characters(Start:=302, Length:=1).Font.Bold = True
End If
Next



THANKS!
Click to expand...
Click to expand...
 
S

Steve Schapel

Janis,

I am sorry, I didn't look closely enough, and assumed from what I saw of
your post that you were working in Exel.

Nevertheless, your question is not related to Macros in Access. This is
a VBA procedure. In Access, Macros are an entirely different species,
and unrelated to VBA.

Anyway, since you're here :) ...

It seems to me that if you want to eliminate the writing of this text at
the end of the page, you can remove the code that assigns the value
"[placeholder]". Therefore, in the second block od code, comment out...
.Cells(c.Row - 2, 1).Value = "[placeholder]"

Not 100% sure, but try that first.
 
G

Guest

Hey thanks, I am learning and I really did think it was Excel like but there
are points in the code where it accesses the data so I didn't know which was
Acceess or Excel. I will try your idea. I just tried commenting out the
loop and I got a runtime error so I will try commenting out the row that
assigns the placeholder.

Steve Schapel said:
Janis,

I am sorry, I didn't look closely enough, and assumed from what I saw of
your post that you were working in Exel.

Nevertheless, your question is not related to Macros in Access. This is
a VBA procedure. In Access, Macros are an entirely different species,
and unrelated to VBA.

Anyway, since you're here :) ...

It seems to me that if you want to eliminate the writing of this text at
the end of the page, you can remove the code that assigns the value
"[placeholder]". Therefore, in the second block od code, comment out...
.Cells(c.Row - 2, 1).Value = "[placeholder]"

Not 100% sure, but try that first.

--
Steve Schapel, Microsoft Access MVP
THIS IS A ACCESS MACRO all three of them. It creates a Excel report in
Access with Access data. I really need help getting the text line out.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro code to partially format font colour of result in cellcontaining a formula 3
Macro that delets all cells containg ALL words in bold 3
Excel Excel Macro works on one computer but errors out on other computers 2
Macro Problems in excel 2007 1
Adjusting height of text cells 3
Manipulating text - 2 queries 1
titles are bold and I don't know why 1
I have created a vba script for a document registers 7

Top