Solved: If Range empty

[markmrw]

quick question


i would like this to goto the label if the range is empty.
i know this is completely wrong but i cant get my brain to do anything let alone my computer :( .

any help would be appreciated




Range("A6:A35").Select
If Selection Is Empty Then
GoTo Label



i hope this is enough information
if not i will post the workbook.

Thankyou in advance

Mark

[lucas]

What do you want it to do when it goes to the label?


Sub a()
If Range("A6:A35").Value = "" Then
' GoTo Label
MsgBox "test"
End If
End Sub

[markmrw]

exit thello lucas
thank you
i would like it to miss out a big lump of code and end the sub

|Mark

[markmrw]

Public Sub Remove_Duplicates()

Response = MsgBox("You Sure", vbYesNo, "WHP")
If Response = vbYes Then



ActiveSheet.Unprotect
ActiveWindow.SmallScroll Down:=-12
Dim rCell As Range


For Each rCell In Range("A6:A35").Cells
If WorksheetFunction.CountIf(Range("A:A"), rCell.Value) > 1 Then
Range("A:A").Replace What:=rCell.Value, Replacement:=""
End If

Next rCell

Else: GoTo Labby
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

'----------------------------------
'here is my problem, i get type mis-match error! (means nothing to me!)
If Range("A6:A35").Value = "" Then
' GoTo Label
MsgBox "test"
End If
'---------------------------------
GoTo Labby



''' Unhide
ActiveSheet.Unprotect
Cells.Select
Selection.EntireColumn.Hidden = False
''' Print
''''''''ActiveWindow.SelectedSheets.PrintPreview '''' if you want preview
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'''Hide
Columns("G:L").Select
Selection.EntireColumn.Hidden = True
'''Protect
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Labby:

End Sub

[lucas]

Maybe this is closer......do you need to go to a certain point before exiting?

Sub Test()

Dim CheckRange As Range
Dim ConstantRange As Range
Dim FormulaRange As Range

Set CheckRange = Sheet1.Range("A6:A35")
On Error Resume Next
Set ConstantRange = CheckRange.SpecialCells(xlCellTypeConstants, 23)
Set FormulaRange = CheckRange.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0

If FormulaRange Is Nothing And ConstantRange Is Nothing Then
MsgBox "Range Is Empty"
Exit Sub
Else
MsgBox "Range Not Empty"
End If

End Sub

[markmrw]

Right!
um i cant claim to understand it, but it doesn't quite work.
as i expected, but i am be told to go home.
i will say thank you now and i will resume fiddling in the morning.
thank you lucas.
Toodle - OOOH

Mark

[lucas]

when you come back, post your workbook if you can with your objectives......

[markmrw]

here is the workbook
all i want it to skip printing if the range is empty

10792

[GTO]

Hey Mark,

Try this...

Public Sub Test()
Dim Response As VbMsgBoxResult
Dim rCell As Range
Dim bolHasVal As Boolean
Response = MsgBox("You Sure", vbYesNo, "WHP")

If Response = vbYes Then

With Worksheets("Sheet1")
.Unprotect

For Each rCell In .Range("A6:A35")
If Not rCell.Value = Empty Then
bolHasVal = True
Exit For
End If
Next

If bolHasVal Then
.Columns("G:L").EntireColumn.Hidden = False
.PrintOut
.Columns("G:L").EntireColumn.Hidden = True
.Protect

End If
End With
End If
End Sub

Seemed to run okay...

Hope this helps,

Mark

[markmrw]

hmm

thank you mark

this does exectly the same as what i already have.:)
i dont know why but it seems that when the range is empty is ignores the "if"

[GTO]

I assure you, I almost always test before I post, and I did in this case.

You want it NOT to print if there's nothing in .Range("A6:A35"), right?

Please repost your workbook with the new code substituted.

Thank you,

Mark

[markmrw]

thank you
Mark

i have it working now,
you code did work. i just had to add a little to make it work as i expexted.
thank you again.
Sorry to doubt your brilliance :)
i was having a "paddy"

Thank you again

Here is the final code
Public Sub Remove_Duplicates()
Dim Response As VbMsgBoxResult
Dim rCell As Range
Dim bolHasVal As Boolean
Response = MsgBox("You Sure", vbYesNo, "WHP")

If Response = vbYes Then

With Worksheets("Sheet1")
.Unprotect

For Each rCell In .Range("A6:A35")
If WorksheetFunction.CountIf(Range("A:A"), rCell.Value) > 1 Then
Range("A:A").Replace What:=rCell.Value, Replacement:=""
End If
Next rCell

For Each rCell In .Range("A6:A35")
If Not rCell.Value = Empty Then
bolHasVal = True
Exit For
End If
Next

If bolHasVal Then
.Columns("G:L").EntireColumn.Hidden = False
.PrintOut
.Columns("G:L").EntireColumn.Hidden = True
.Protect

End If
End With
End If
End Sub


One other question Is "Bol" = Boolean?, or am i reading this wrong.

[GTO]

Hey Mark,

Not much chance of brilliance from this end, but happy to help. After all, ya gotta be a great guy with Mark for a name :)

[GTO]

Sorry, too many people yacking at me when I replied... I forgot to answer your other question.

As to "bol", yes, this is a relatively common notation (or preface) for indicating a Boolean variable. Now just to make sure (don't take any offense at all) as it seems like I have miscommunicated with several people over the last couple of days... The "bol" notation isn't what makes it a Boolean, it is the As keyword and Boolean assignment. Again - I'm sure you already had that, but as I said, I've had several things misunderstood lately, so better a few more words and ensure clarity.

While you will occassionally hear or read various arguments for or against Hungarian or other types of notation, I personally hold the opinion that the goal of the written word (or spoken for that matter) is to communicate the idea, 'picture', emotion, or other, as accurately as possible, and that arguing that one word or language (not compuer language, more at English vs. Spanish vs. Dutch) is better than another is just silly. Either the reader easily grasps (succesful language) what is written or they don't (in which cases the language or coding [bleeps]!).

For instance, Steve Lucas' naming is very nice and clear and easily readable as well.

Dim CheckRange As Range
Dim ConstantRange As Range
Dim FormulaRange As Range

While I'm sure there are plenty who could explain this better, my summation would be that the more intutive (from the reader's perspective) naming is, the better.

Well... that was way more than you probably wanted?

Have a great night,

Mark

[markmrw]

Ah. thank you for the indepth repsonse.
so many things become clear.
this all helps in my goal to become a brilliant coder. :)
I appreciate all the help that everyone has given, hopefully one day i will be able to repay the favour.

Since using this forum i have realised, that there is almost "no Limit" to what you can do, (that is if you can find the right code/expertise)

Thank you again.


Mark

Power to the Mark's

[GTO]

Hey Mark,

I already PM'd you, and hope you catch this. I happened to be flipping thru threads last night (a bit o' insomnia) and re-read this. My apologies, but one little change needed, else the sheet may be left unprotected.

Sorry 'bout that,

Mark
Public Sub Remove_Duplicates()
Dim Response As VbMsgBoxResult
Dim rCell As Range
Dim bolHasVal As Boolean
Response = MsgBox("You Sure", vbYesNo, "WHP")

If Response = vbYes Then

With Worksheets("Sheet1")
.Unprotect

For Each rCell In .Range("A6:A35")
If WorksheetFunction.CountIf(Range("A:A"), rCell.Value) > 1 Then
Range("A:A").Replace What:=rCell.Value, Replacement:=""
End If
Next rCell

For Each rCell In .Range("A6:A35")
If Not rCell.Value = Empty Then
bolHasVal = True
Exit For
End If
Next

If bolHasVal Then
.Columns("G:L").EntireColumn.Hidden = False
.PrintOut
.Columns("G:L").EntireColumn.Hidden = True
'// YEEKS! I misdirected you. My apologies, as this will leave //
'// the sheet unprotected, in the case where nothing is found in //
'// A6:A35 . //
'.Protect
End If

'// Move the .Protect here, outside of the IF. Again, my bad... //
.Protect
End With
End If
End Sub