I have a simple script to print all rows of input into a formatted output. I would like to skip any row where column A is empty, but still print if there is data, so row 1 skip, row 2 print, row 3 print, row 4 skip, etc, based on column A. How can I do that? I have tried numerous codes with no results. Here is my current working code which prints all.
Sub PrintAllRecords()
RowCount = Worksheets("Input").Cells(Rows.Count, 1).End(xlUp).Row - 1
Worksheets("Print-Auto (2)").Select
For i = 1 To RowCount
Range("B1").Value = i
ActiveSheet.PrintOut Copies:=1
Next i
End Sub
CodePudding user response:
This should work:
Sub PrintAllRecords()
Dim inputSheet As Worksheet: Set inputSheet = ThisWorkbook.Worksheets("input")
RowCount = inputSheet.Cells(Rows.Count, 1).End(xlUp).Row - 1
Worksheets("Print-Auto (2)").Select
For i = 1 To RowCount
If Not IsEmpty(inputSheet.Cells(i, 1)) Then
Range("B1").Value = i
ActiveSheet.PrintOut Copies:=1
End If
Next i
End Sub
CodePudding user response:
Print Non-Blanks Using AutoFilter
- This will print the rows of the used range where the first column is not blank.
- Adjust the values in the constants section.
Option Explicit
Sub PrintNonBlanks()
Const dFolderPath As String = "C:\Test\"
Const dFileName As String = "Test.pdf"
Const wsName As String = "Sheet1"
Const CriteriaColumnNumber As Long = 1
Const CriteriaString As String = "<>" ' non-blanks
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containinig this code
' Create a reference to the worksheet.
Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
' Remove any previous filter.
If ws.AutoFilterMode Then ws.AutoFilterMode = False
' Create a reference to the worksheets used range.
Dim urg As Range: Set urg = ws.UsedRange
' Filter first column by non-blanks.
urg.AutoFilter CriteriaColumnNumber, CriteriaString
' Test with either...
urg.ExportAsFixedFormat xlTypePDF, dFolderPath & dFileName
' ... or...
'ws.ExportAsFixedFormat xlTypePDF, dFolderPath & dFileName
' ... before printing...
'urg.PrintOut
' or...
'ws.PrintOut
' Remove the filter.
ws.AutoFilterMode = False
' Locate the file (folder) in Windows File Explorer.
'wb.FollowHyperlink dFolderPath
End Sub
CodePudding user response:
Try this one
Sub PrintAllRecords()
RowCount = Worksheets("Input").Cells(Rows.Count, 1).End(xlUp).Row - 1
Worksheets("Print-Auto (2)").Select
For i = 1 To RowCount
If Not IsEmpty(Worksheets("Input").Range("A" & i 1)) Then
msgbox i
Range("B1").Value = i
ActiveSheet.PrintOut Copies:=1
End If
Next i
End Sub