Creating changing print ranges

[JohnZ1156]

I have an Excel table.
I want to print from the ActiveCell (for now A6771) to column K and last row in table.

The ActiveCell will change from month to month, will not always be A6771.
Need to "highlight" or select from the ActiveCell to column K and the last row in table.
Set print area and print.

 

[Trebor76]

Hi JohnZ1156,

Welcome to MrExcel!!

Try this while on the tab to have the range printed:

Option Explicit
Sub Macro2()

    Dim lngRowFrom As Long
    Dim lngRowTo As Long
    
    Application.ScreenUpdating = False
    
    On Error Resume Next 'Stop error message for setting 'lngRowTo' variable in case there's no data on the sheet
        lngRowFrom = ActiveCell.Row
        lngRowTo = Range("A:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    
    If lngRowFrom > 0 And lngRowTo > 0 Then
        If lngRowTo >= lngRowFrom Then
            Range(Cells(lngRowFrom, ActiveCell.Column), Cells(lngRowTo, "K")).PrintOut Copies:=1
        End If
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[JohnZ1156]

Hi Robert,

That works great.
Thank you so very much.

 

[Trebor76]

Thanks for letting us know and you're welcome

 

[JohnZ1156]

Hi Robert,
I have another question if you don't mind.

I have a macro that searches a checking account table for a specific check number in the Number column.
If the number entered does not exist, it returns a message box.

Here it is:

Sub FindMatchingValue()
    Dim i As Integer, intValueToFind As Integer
    Dim myString As String

myString = ThisWorkbook.Sheets("Sheet1").Range("o20").Value - 1

    intValueToFind = InputBox("What check number are you looking for?" & Chr(13) & Chr(13) & "Valid numbers:    101 - 1450" & Chr(13) & "                              1501 - " & myString)

'    intValueToFind = 1011
    For i = 18 To 10000    ' Revise the 500 to include all of your values
        If Cells(i, 3).Value = intValueToFind Then
'            MsgBox ("Found value on row " & i)
             Rows(i).Select
             Range("A" & (ActiveCell.Row)).Select
             Range(Cells(Selection.Row, 1), Cells(Selection.Row, 11)).Select
            Exit Sub
        End If
    Next i

    ' This MsgBox will only show if the loop completes with no success
    MsgBox ("Value " & intValueToFind & " not found in the Check Register range!")

    Range("B1048576").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select

End Sub

I have another macro that searches for text in all columns rather that a value.
Here it is:

Sub FindLastOccurance()
    Dim C As Range, where As Range, whatt As String
    Dim MyLastRow As String
    Dim response As String

    MyLastRow = Range("A65536").End(xlUp).Row

  response = InputBox("What do you want to search for?")
'  MsgBox "The variable is " + response

    whatt = response
    Set C = Range("a:k")
    Set where = C.Find(What:=whatt, After:=C(1), SearchDirection:=xlPrevious)
'    MsgBox where.Address(0, 0)

'On Error GoTo ErrorHandler

   ActiveSheet.Range(where.Address(0, 0)).Select
'   Range("A" & (ActiveCell.Row)).Select
'   ActiveSheet.Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 11)).Select

'ErrorHandler:
'    MsgBox ("The search text " + response + " does not exist.")
'    Exit Sub

'   ActiveCell.EntireRow.Select
'   ActiveCell.Offset(0, -9).Select

'   Range(ActiveCell & ":K" & MyLastRow).Select
'   MsgBox newRange.Address
   Range("A" & (ActiveCell.Row)).Select
   ActiveSheet.Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 11)).Select

End Sub

I know there's a lot of "comments", That's just my trials and errors.

I'd like this macro to check for errors like the first macro.
If the text entered does not exist, display a message box stating, "The text you entered does not exist." and exit the macro.

Can you please help me again,
Frustrated VBA virgin,
John

 

[Fluff]

How about

Sub JohnZ()
   Dim Response As String
   Dim Fnd As Range
   
   Response = InputBox("What do you want to search for?")
   If Response = "" Then Exit Sub
   
   Set Fnd = Range("A:K").Find(Response, , , xlWhole, xlByRows, xlPrevious, False, , False)
   If Fnd Is Nothing Then
      MsgBox "The search text " + Response + " does not exist."
      Exit Sub
   End If
   Range("A" & Fnd.Row).Resize(, 11).Select
End Sub

 

[JohnZ1156]

How about

Sub JohnZ()
   Dim Response As String
   Dim Fnd As Range
  
   Response = InputBox("What do you want to search for?")
   If Response = "" Then Exit Sub
  
   Set Fnd = Range("A:K").Find(Response, , , xlWhole, xlByRows, xlPrevious, False, , False)
   If Fnd Is Nothing Then
      MsgBox "The search text " + Response + " does not exist."
      Exit Sub
   End If
   Range("A" & Fnd.Row).Resize(, 11).Select
End Sub

Thanks,
But when I run it now, and type text that I can see exists, I get the message that my text does not exist.

 

[Fluff]

Are you keying in a partial or complete match?

 

[JohnZ1156]

Are you keying in a partial or complete match?

I guess partial,
For example: If I'm looking for the last time I wrote a check to Dr. Smith, and the record says, Dr. John Smith, DMD, PA, I would like to just type in Smith.
Or if I'm looking for the last time I wrote a check for my pharmacy, and the record says, Colonial Pharmacy, I'd like to be able to simply type Pharm.
It could be all or part of the existing text in the field.

 

[Fluff]

In that case change xlWhole to xlPart