Windows XP 
Access 2007
When I export to Excel, the worksheet ends up with an extra formatted blank row. How can I prevent this from happening or delete it after it did? I'm only including the code for one of the Tabs, as it happens to them all and the fix will most likely be the same for each one.
Code:'************************************************************************************************************************************************************************************************'************************************************************************************************************************************************************************************************ 'SECOND TAB - Pending PRs '************************************************************************************************************************************************************************************************ '************************************************************************************************************************************************************************************************ Set x1Sheet = x1Book.Worksheets.Add(After:=x1Book.Worksheets(1)) With x1Sheet .Name = "Pending" .Cells.Font.Name = "Calibri" .Cells.Font.Size = 11 'End With 'SQL Statement to retrieve data from database SQL = "Select [Date Procurement Rec'd PR], [Reason for PR Hold], [Date of Purchasing Hold], [Date of Hold Release], [Customer Order Number], [PR Line Status], FRC, [DTO-STOCK], [Part Number], [NSN/LSN], Description, [PR QTY], UOM, [Material Required Date], [NC/NIS], [Buyer ID] FROM qry600_FRCE_Pending Order by [Date Procurement Rec'd PR]" 'Execute query and populate recordset Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot) 'If no data, don't bother opening excel, just quit If rs1.RecordCount = 0 Then MsgBox "No Data selected for export", vbInformation + vbOKOnly, "No Data Exported" GoTo SubExit End If '************************************************************ 'Set New Worksheet '************************************************************ 'Set Column Widths: .Columns("A").ColumnWidth = 12 .Columns("B").ColumnWidth = 14 .Columns("C").ColumnWidth = 12 .Columns("D").ColumnWidth = 12 .Columns("E").ColumnWidth = 12 .Columns("F").ColumnWidth = 15 .Columns("G").ColumnWidth = 20 .Columns("H").ColumnWidth = 8 .Columns("I").ColumnWidth = 30 .Columns("J").ColumnWidth = 16 .Columns("K").ColumnWidth = 40 .Columns("L").ColumnWidth = 8 .Columns("M").ColumnWidth = 8 .Columns("N").ColumnWidth = 11 .Columns("O").ColumnWidth = 5 .Columns("P").ColumnWidth = 6 'Set Row Heights: .Rows("1").RowHeight = 45 .Rows("2").RowHeight = 25 .Rows("3").RowHeight = 0 'Format colums: .Columns("A").NumberFormat = "MM/DD/YYYY" .Columns("A").HorizontalAlignment = xlCenter .Columns("A").WrapText = True .Columns("A").VerticalAlignment = xlCenter ' .Columns("B").NumberFormat = "@" .Columns("B").HorizontalAlignment = xlCenter .Columns("B").WrapText = True .Columns("B").VerticalAlignment = xlCenter ' .Columns("C").NumberFormat = "MM/DD/YYYY" .Columns("C").HorizontalAlignment = xlCenter .Columns("C").WrapText = True .Columns("C").VerticalAlignment = xlCenter ' .Columns("D").NumberFormat = "MM/DD/YYYY" .Columns("D").HorizontalAlignment = xlCenter .Columns("D").WrapText = True .Columns("D").VerticalAlignment = xlCenter ' .Columns("E").NumberFormat = "#########" .Columns("E").HorizontalAlignment = xlCenter .Columns("E").WrapText = True .Columns("E").VerticalAlignment = xlCenter ' .Columns("F").NumberFormat = "000000000" .Columns("F").HorizontalAlignment = xlCenter .Columns("F").WrapText = True .Columns("F").VerticalAlignment = xlCenter ' .Columns("G").NumberFormat = "@" .Columns("G").HorizontalAlignment = xlCenter .Columns("G").WrapText = True .Columns("G").VerticalAlignment = xlCenter ' .Columns("H").NumberFormat = "#####" .Columns("H").HorizontalAlignment = xlCenter .Columns("H").WrapText = True .Columns("H").VerticalAlignment = xlCenter ' .Columns("I").HorizontalAlignment = xlCenter .Columns("I").WrapText = True .Columns("I").VerticalAlignment = xlCenter ' .Columns("J").NumberFormat = "#############" .Columns("J").HorizontalAlignment = xlCenter .Columns("J").WrapText = True .Columns("J").VerticalAlignment = xlCenter .Columns("K").HorizontalAlignment = xlLeft .Columns("K").WrapText = True .Columns("K").VerticalAlignment = xlCenter ' .Columns("L").NumberFormat = "#####" .Columns("L").HorizontalAlignment = xlCenter .Columns("L").WrapText = True .Columns("L").VerticalAlignment = xlCenter ' .Columns("M").NumberFormat = "@" .Columns("M").HorizontalAlignment = xlCenter .Columns("M").WrapText = True .Columns("M").VerticalAlignment = xlCenter ' .Columns("N").NumberFormat = "MM/DD/YYYY" .Columns("N").HorizontalAlignment = xlCenter .Columns("N").WrapText = True .Columns("N").VerticalAlignment = xlCenter ' .Columns("O").NumberFormat = "@" .Columns("O").HorizontalAlignment = xlCenter .Columns("O").WrapText = True .Columns("O").VerticalAlignment = xlCenter ' .Columns("P").NumberFormat = "@" .Columns("P").HorizontalAlignment = xlCenter .Columns("P").WrapText = True .Columns("P").VerticalAlignment = xlCenter .Columns("Z").NumberFormat = "@" .Columns("Z").HorizontalAlignment = xlCenter .Columns("Z").WrapText = True .Columns("Z").VerticalAlignment = xlCenter .Columns("AA").NumberFormat = "@" .Columns("AA").HorizontalAlignment = xlLeft .Columns("AA").WrapText = True .Columns("AA").VerticalAlignment = xlCenter 'Add Worksheet Header and Date .Range("A1", "P1").Merge .Range("A2", "P2").Merge .Range("A1").HorizontalAlignment = xlCenter .Range("A2").HorizontalAlignment = xlCenter .Range("A1").Cells.Font.Size = 20 .Range("A2").Cells.Font.Size = 16 .Range("A1").Cells.Font.Bold = True .Range("A2").Cells.Font.Bold = True .Range("A1").Cells.Font.Name = "Cambria" .Range("A2").Cells.Font.Name = "Cambria" .Range("A1").Value = "FRCE A009 Weekly Order Status Report (WOSR)" .Range("A2").Value = Date 'Build Column Headings .Range("A4").Value = "Date Procurement Rec'd PR" .Range("B4").Value = "Reason for PR Hold" .Range("C4").Value = "Date of Purchasing Hold" .Range("D4").Value = "Date of Hold Release" .Range("E4").Value = "Customer Order Number" .Range("F4").Value = "PR Line Status" .Range("G4").Value = "FRC" .Range("H4").Value = "DTO / STOCK" .Range("I4").Value = "Part Number" .Range("J4").Value = "NSN / LSN" .Range("K4").Value = "Description" .Range("L4").Value = "PR QTY" .Range("M4").Value = "UOM" .Range("N4").Value = "Material Required Date" .Range("O4").Value = "NC / NIS" .Range("P4").Value = "Buyer ID" 'Format Columns Heading .Range("A4:P4").HorizontalAlignment = xlCenter .Range("A4:P4").Cells.Font.Bold = True .Range("A4:P4").Interior.Color = RGB(217, 217, 217) 'provide initial value to row counter i = 5 'Loop through recordset and copy data from recordset to sheet Do While Not rs1.EOF .Range("A" & i).Value = Nz(rs1![Date Procurement Rec'd PR]) .Range("B" & i).Value = Nz(rs1![Reason for PR Hold]) .Range("C" & i).Value = Nz(rs1![Date of Purchasing Hold], "") .Range("D" & i).Value = Nz(rs1![Date of Hold Release], "") .Range("E" & i).Value = Nz(rs1![Customer Order Number], "") .Range("F" & i).Value = Nz(rs1![PR Line Status], "") .Range("G" & i).Value = Nz(rs1!FRC, "") .Range("H" & i).Value = Nz(rs1![DTO-STOCK], "") .Range("I" & i).Value = Nz(rs1![Part Number], "") .Range("J" & i).Value = Nz(rs1![NSN/LSN], "") .Range("K" & i).Value = Nz(rs1!Description, "") .Range("L" & i).Value = Nz(rs1![PR QTY], "") .Range("M" & i).Value = Nz(rs1!UOM, "") .Range("N" & i).Value = Nz(rs1![Material Required Date], "") .Range("O" & i).Value = Nz(rs1![NC/NIS], "") .Range("P" & i).Value = Nz(rs1![Buyer ID]) i = i + 1 rs1.MoveNext Loop 'Build Grid lines .Range("A4", "P4").Borders(xlEdgeTop).LineStyle = XlLineStyle.xlContinuous .Range("A4:A" & i).Borders(xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous .Range("A4:P" & i).Borders(xlEdgeRight).LineStyle = XlLineStyle.xlContinuous .Range("A4:P" & i).Borders(xlInsideVertical).LineStyle = XlLineStyle.xlContinuous .Range("A4:P" & i).Borders(xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous .Range("A4:P" & i).Borders(xlEdgeBottom).Weight = XlBorderWeight.xlThick End With
Reply With Quote
