Forum Discussion

eesamp's avatar
eesamp
Copper Contributor
Dec 08, 2020

How to use vba to unselect a row after use

I have a workbook with multiple sheets. On the master sheet, I "find" a name, the run a vba application to use the data in the row selected by the "find." When that application completes, it returns to the master sheet at the same position, with the row highlighted (selected). I need to unselect that row. If I do not, the "find" routine will not move off the row, it just "finds" the same thing. If I type a different name in the "find", it produces an error message "cannot find what you are looking for...

 

I have not come up with anything short of forcing the position in the sheet to go back to the beginning.  Any help would be much appriciated.

 

excel

7 Replies

    • eesamp's avatar
      eesamp
      Copper Contributor
      Dec 09, 2020

      Jihad Al-Jarady 

      A preface, I use the standard "Find" function to locate a member record on the "master" sheet," then hit a macro button to run my routine. When that finishes up, I return tho the "master" sheet at the same record. It is still selected by row. I need fot this to become "UnSelected" but still showing. (For now I have it returning at the top in "D2". Here is the code for the macro...

       

      Sub ToPrSheet()
      '
      ' ToPrSheer Macro
      ' ***USES THE STANDARD "fIND" TO GET THE MEMBER RECORD AND GET TO HERE***
      ' Only one label at a time can use this method. Any more wil need
      ' the use of the MailMerge function of a Word document
      '
      ' Copies one row to PrSheet for printing label (eventually)
      ' This gets the active row on from "HPRAmasterDB2" sheet...
      ' copies it to row A on "PrSheet"...
      ' formats the Name, Address and LDP of the member...
      ' then sets up for printing to a label printer
      ' A printer list is provided to select the proper one

      Dim rownumber As Integer
      Worksheets("HPRAmasterDB2").Activate
      ' Set the row number and fall through for now - may add some procesing later...
      rownumber = ActiveCell.Row
      If ActiveCell.Value <> "" Then
      End If
      ' Select and copy the row
      Rows(rownumber).Select
      Selection.Copy
      ' Activate thr "Printing sheet" and point to cell "A1"
      Sheets("PrSheet").Select
      Range("A1").Select
      ' Fill row "A" starting with cell "A1"
      Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
      ActiveSheet.Paste 'Columns A2:A4 auto format the mailing label
      ' Set column "A" to 36 points wide and set the print area...
      Worksheets("PrSheet").Range("A1").ColumnWidth = 36
      Worksheets("PrSheet").PageSetup.PrintArea = "$A$2:$A$4"
      ' Select the proper printer from the list (ie: A Label Printer)...
      Application.Dialogs(xlDialogPrinterSetup).Show
      ' Preview it...
      Worksheets("PrSheet").PrintPreview
      ' ...and Print the label
      ' TURNED OFF DURING DEBUGGING TO SAVE LABELS
      ' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
      ' Return to master sheet
      Worksheets("HPRAmasterDB2").Activate 'and un select the highlighted row but stay there
      Range("D2").Select 'temporary until an "UnSelect Row method is found

      End Sub

      • Jihad Al-Jarady's avatar
        Jihad Al-Jarady
        MCT
        Dec 09, 2020

        eesamp

         

        Add this code at the end :

        Application.CutCopyMode 

         

         

         

        Sub ToPrSheet()
        '
        ' ToPrSheer Macro
        ' ***USES THE STANDARD "fIND" TO GET THE MEMBER RECORD AND GET TO HERE***
        ' Only one label at a time can use this method. Any more wil need
        ' the use of the MailMerge function of a Word document
        '
        ' Copies one row to PrSheet for printing label (eventually)
        ' This gets the active row on from "HPRAmasterDB2" sheet...
        ' copies it to row A on "PrSheet"...
        ' formats the Name, Address and LDP of the member...
        ' then sets up for printing to a label printer
        ' A printer list is provided to select the proper one

        Dim rownumber As Integer
        Worksheets("HPRAmasterDB2").Activate
        ' Set the row number and fall through for now - may add some procesing later...
        rownumber = ActiveCell.Row
        If ActiveCell.Value <> "" Then
        End If
        ' Select and copy the row
        Rows(rownumber).Select
        Selection.Copy
        ' Activate thr "Printing sheet" and point to cell "A1"
        Sheets("PrSheet").Select
        Range("A1").Select
        ' Fill row "A" starting with cell "A1"
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        ActiveSheet.Paste 'Columns A2:A4 auto format the mailing label
        ' Set column "A" to 36 points wide and set the print area...
        Worksheets("PrSheet").Range("A1").ColumnWidth = 36
        Worksheets("PrSheet").PageSetup.PrintArea = "$A$2:$A$4"
        ' Select the proper printer from the list (ie: A Label Printer)...
        Application.Dialogs(xlDialogPrinterSetup).Show
        ' Preview it...
        Worksheets("PrSheet").PrintPreview
        ' ...and Print the label
        ' TURNED OFF DURING DEBUGGING TO SAVE LABELS
        ' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        ' Return to master sheet
        Worksheets("HPRAmasterDB2").Activate 'and un select the highlighted row but stay there
        Range("D2").Select 'temporary until an "UnSelect Row method is found

         

        Application.CutCopyMode 
        End Sub

Resources