Print If Column Contains Blanks (No Characters)

[Guest]

Well guys this is my first Excel Experience!

I have to check if Column HSCrs has No Data.

If it does not I print out the Contents of Column 4 and Column 5.

If it does have Data I have to do another print out, but more important
create another Workbook.

The code I have below works so far, except I Do not know where to put my
AutoFilter and How to Check for the Column (HSCrs) having Blanks.

If HSCrs has data, and the Column FSJS Contains "5-A" or "Sophomore" than I
copy that Line to my new WorkBook.

I would like to get a little done so when I get to school tomorrow I can
find out more!

Here is my Code!

Sub Five-A()
Dim wks As Worksheet ' Current Sheet
Dim lls As Workbook
Dim wsl As Worksheet ' Work Sheet
Dim FSJS As String
Dim FSJSNo As Long
Dim HSCrs As String
Dim HSCrsNo As Long

Set wks = ActiveSheet
FSJS = InputBox("Enter the Column for the Class!")
FSJSNo = Columns(FSJS & ":" & FSJS).Column ' Class Col #
HSCrs = InputBox("Enter the Column for Course to be Reviewed!")
HSCrsNo = Columns(HSCrs & ":" & HSCrs).Column ' Course Col #

Set lls = Workbooks.Add

With lls
.Title = "Valdez High School"
.Subject = "Sophomores"
End With

' Selection.AutoFilter Field:=12, Criteria1:="5-A"

Set wsl = ActiveSheet
wks.Columns(FSJS).Copy wsl.Columns(1)
wks.Columns(249).Copy wsl.Columns(2)
wks.Columns(251).Copy wsl.Columns(3)
wks.Columns(252).Copy wsl.Columns(4)
wks.Columns(253).Copy wsl.Columns(5)
wks.Columns(254).Copy wsl.Columns(6)
wks.Columns(HSCrs).Copy wsl.Columns(7)


With wsl.Range("A:H").EntireColumn
.ColumnWidth = 255
.AutoFit
End With

wsl.SaveAs Filename:="C:\Valdez\Geography.xls",
FileFormat:=xlNormal

End Sub

Thanks a Bunch Guys

Nancy X.

 

[Guest]

Found Out How To Finds Blanks, Changed my own code below. However still
trying to figure out how I can filter two columns.

Well guys this is my first Excel Experience!

I have to check if Column HSCrs has No Data.

If it does have Data I have to do another print out, but more important
create another Workbook.

The code I have below works so far, except I Do not know where to put my
AutoFilter and How to Check for the Column (HSCrs) having Blanks.

If HSCrs has data, and the Column FSJS Contains "5-A" or "Sophomore" than I
copy that Line to my new WorkBook.

I would like to get a little done so when I get to school tomorrow I can
find out more!

Here is my Code!

Sub Five-A()
Dim wks As Worksheet ' Current Sheet
Dim lls As Workbook
Dim wsl As Worksheet ' Work Sheet
Dim FSJS As String
Dim FSJSNo As Long
Dim HSCrs As String
Dim HSCrsNo As Long

Set wks = ActiveSheet
FSJS = InputBox("Enter the Column for the Class!")
FSJSNo = Columns(FSJS & ":" & FSJS).Column ' Class Col #
HSCrs = InputBox("Enter the Column for Course to be Reviewed!")
HSCrsNo = Columns(HSCrs & ":" & HSCrs).Column ' Course Col #

Set lls = Workbooks.Add

With lls
.Title = "Valdez High School"
.Subject = "Sophomores"
End With

Selection.AutoFilter Field:=HSCrsNo, Criteria1:=""

Need to know how to send Columns 4 and 5 to a printer?

Then I guess I need to do another Filter for Non-Blanks?

 

[Guest]

Update From Nancy

Well a lot has happened today, lost all of my Macros twice.

Confused by All Workbooks, This WorkBook and Valdezcls Workbook.

I had to rekey everything, but most of it was from different users. Here is
my current code

Dim wks As Worksheet ' Current Sheet
Dim lls As Workbook
Dim wsl As Worksheet ' Work Sheet in Memory
Dim rng As Range
Dim FSJS As String
Dim FSJSNo As Long
Dim HSCrs As String
Dim HSCrsNo As Long

Set wkst = ActiveSheet

FSJS = InputBox("Enter Grade, Class, or 5-A On Student, Column
Heading")
ColNum = Columns(FSJS & ":" & FSJS).Column
HSCrs = InputBox("Enter the Column for the Course to be Reviewed")
HSCrsNo = Columns(HSCrs & ":" & HSCrs).Column

Set lls = Workbooks.Add(1)
Set wsl = lbls.Worksheets(1)
lls.Title = "Valdez High School"
lls.Subject = "Sophomores"

' Find Errors Routine

If FSJS = "10" then FSJSNo = 19
If FSJS = "5-A" then FSJSNo = 245

With wks
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=FSJSNo, Criteria1:="Sophomore"
.AutoFilter Field:=HSCrsNo, Criteria1:=""
.Columns(FSJS).Copy wslb.Columns(1).Cells(1)
.Columns(249).Copy wslb.Columns(2).Cells(1)
.Columns(251).Copy wslb.Columns(3).Cells(1)
.Columns(252).Copy wslb.Columns(4).Cells(1)
.Columns(253).Copy wslb.Columns(5).Cells(1)
.Columns(254).Copy wslb.Columns(5).Cells(1)
.Columns(HSCrs).Copy wslb.Columns(6).Cells(1)
End With
End With

With wsl.Range("A:G").EntireColumn
.ColumnWidth = 255
.AutoFit
End With

With wkst.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = "$A:$D"
.LeftHeader = ""
'Error .CenterHeader = "&" "Arial,Bold" "Information Missing on Student
Record Report"
.RightHeader = ""
.LeftFooter = "&8&D"
.CenterFooter = ""
.RightFooter = "&8&T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
' Error .PrintErrors = xlPrintErrorsDisplayed
End With
' Error ActiveWindow.SelectedSheets.PrintPreview , Collate:=True

wslb.SaveAs Filename:="C:\Valdez\Geography.xls", FileFormat:=xlNormal

The lines marked with ' Error are where I got halts.

Are all of these lines for the printer necessary.
Before I tried to guess what the format was for Print Preview, I lost one
Alaskan Tree.

I want to review the errors before I continue to save a new WorkBook.

Doing better, but still need a lot of help!

Nancy X