remove a text row in Excel rpt before the pagebreak with [Placehol Janis 18.08.2006 18:41:02 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!

Re: remove a text row in Excel rpt before the pagebreak with [Placehol Steve Schapel <schapel[ at ]mvps.org.ns> 18.08.2006 19:08:12 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 Janis wrote: [Quoted Text] > 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! >

Re: remove a text row in Excel rpt before the pagebreak with [Plac Janis 18.08.2006 19:22:01 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" wrote: [Quoted Text] > 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 > > Janis wrote: > > 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! > > >

Re: remove a text row in Excel rpt before the pagebreak with [Plac Steve Schapel <schapel[ at ]mvps.org.ns> 18.08.2006 19:47:05 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 Janis wrote: [Quoted Text] > 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.

Re: remove a text row in Excel rpt before the pagebreak with [Plac Janis 18.08.2006 22:17:03 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" wrote: [Quoted Text] > 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 > > Janis wrote: > > 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. >