CODEWITHSUNDEEP
vbaexcel
Jacob Lenertz
Jacob Lenertz

Reputation: 81

Excel Selecting a Row

I am Trying to create a macro that will take the 1st Cell of a selection and merge it with the 6th Cell. I am getting error 400.

Sub SignPage()
Dim ws As Worksheet, BasePoint As Range, row As Long, singlecell As Long, 
singleCol As Long, sLong As Long, sShort As String
Set BasePoint = Selection
row = BasePoint.row
Range(Cells(row, BasePoint.Columns(1)), Cells(row, BasePoint.Columns(6))).Merge

End Sub

Upvotes: 1

Views: 100

Answers (2)

Vityata
Vityata

Reputation: 43595

The point is that you should refer to the first column of Basepoint and take its column. This could be achieved, if you amend your line like to this:

Range(Cells(row,BasePoint.Columns(1).Column),Cells(row,BasePoint.Columns(6).Column)).Merge

As an advice:

  • do not use names like row, column, Cells, Range etc. as a variable, the Excel object module library inside VBA uses them as well. In your case, you have BasePoint.row. If you did not have the variable row, then the VBEditor would have written BasePoint.Row automatically.

  • try to avoid Selection, although it depends on the context - How to avoid using Select in Excel VBA

  • as @Mat's Mug mentioned in the comments, it is a good habit to show the "parent" of Cells and Ranges, when you are using them. Thus in your case, something like this:

With Worksheets(1)
       .Range(.Cells(row, BP.Columns(1).Column), .Cells(row, BP.Columns(6).Column)).Merge
End With

Then it would always refer to the first worksheet (or whichever you need to) and not to the ActiveSheet, which would be referred, if the Parent in not mentioned. (I have written BP instead of BasePoint to make sure it goes on one line.)

Upvotes: 2

JohnyL
JohnyL

Reputation: 7162

This should be enough:

Selection(1).Resize(, 6).Merge

Explanation: Selection can span several cells, so Selection(1) takes the top left cell - thus, we always refer to one cell.

Upvotes: 3

Related Questions