Need VBA code that selects specified cells of an entered row number

Snake Eyes

Board Regular
Joined
Dec 14, 2010
Messages
103
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a macro that allows me to select and entire row:
Excel Formula:
Sub Select_Specific_Row()
    
    Dim Row As String
    Dim rng As Range

StartHere:
    Row = InputBox("Enter Row Number to Select.", "Select Row")
    If Row = "" Then Exit Sub
    On Error Resume Next
    ' if not a valid range, an error occurs
    Set rng = ActiveSheet.Rows(Row)
    If Err.Number <> 0 Then
        On Error GoTo 0
        Err.Clear
        MsgBox "Invalid input! Please input a valid column."
        GoTo StartHere
    End If

    rng.EntireRow.Select = True

    MsgBox "Row" & UCase(Row) & "is now Selected.", _
      vbOKOnly, "Select Specified Row"
    Set rng = Nothing
    On Error GoTo 0
End Sub

With this macro I will be copying the selected row to other sheets however, one thing I need to do is select a certain range of cells in the entered row and then clear contents of that range.
These are the cells I would like to work into the code above in order to select them:
A:D,K,L,O:Q,T,U
How would I work these into the code above?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi Snake Eyes,

maybe

VBA Code:
Sub Select_Specific_Row_UnionRange()
    
  Dim rng As Range
  Dim lRow As Long

  On Error Resume Next
  Set rng = Application.InputBox("Enter Cell Address like A1 to Select.", "Select Cell", Type:=8)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub

  rng.EntireRow.Copy
  Worksheets.Add after:=Worksheets(Worksheets.Count)
  ActiveSheet.Paste
  
  rng.Parent.Select
  lRow = rng.Row

  MsgBox "Row " & lRow & " is now Selected.", vbOKOnly, "Select Specified Row"
  Range("A" & lRow & ":D" & lRow & ",K" & lRow & ":L" & lRow & ",O" & lRow & ":Q" & lRow & ",T" & lRow & ":U" & lRow).ClearContents
  Set rng = Nothing

End Sub

or

VBA Code:
Sub Select_Specific_Row_Cells()
  
  Dim rng As Range
  Dim lRow As Long

  On Error Resume Next
  Set rng = Application.InputBox("Enter Cell Address like A1 to Select.", "Select Cell", Type:=8)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub

  rng.EntireRow.Copy
  Worksheets.Add after:=Worksheets(Worksheets.Count)
  ActiveSheet.Paste
  
  rng.Parent.Select
  lRow = rng.Row

  MsgBox "Row " & lRow & " is now Selected.", vbOKOnly, "Select Specified Row"
  
  Set rng = Union(Range(Cells(lRow, "A"), Cells(lRow, "D")), Range(Cells(lRow, "K"), Cells(lRow, "L")))
  Set rng = Union(rng, Range(Cells(lRow, "O"), Cells(lRow, "Q")), Range(Cells(lRow, "T"), Cells(lRow, "U")))
  rng.ClearContents
  Set rng = Nothing

End Sub

Ciao,
Holger
 
Upvote 0
Solution

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top