1

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
braX
  • 11,506
  • 5
  • 20
  • 33
Jacob Lenertz
  • 81
  • 1
  • 13

2 Answers2

3

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.

JohnyL
  • 6,894
  • 3
  • 22
  • 41
  • This should be the accepted answer. Only missing a type check on `Selection`, and perhaps a cast to `Range` to get IntelliSense, but this won't throw any error if the selection isn't at least 6 columns wide. – Mathieu Guindon Dec 20 '17 at 17:55
  • 1
    @Mat'sMug Yes, the `Selection` can span several cells, so I edited answer :) – JohnyL Dec 20 '17 at 18:04
2

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.)

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 2
    FWIW there is nothing wrong with `cell`, `row`, `column`, etc.; VBA does not use them at all. VBA isn't the Excel object model, and the Excel object model isn't VBA. Besides there's no `Row` or `Column` or `Cell` global-scope object in the Excel object model. So the worst it can do is make `Range.Row` appear as `Range.row`, which has no impact whatsoever. `row` and `column` (and `cell`) are perfectly fine local variable names, and they're not shadowing anything. The unqualified `Cells` and `Range` calls are much more dangerous of a habit to have. – Mathieu Guindon Dec 20 '17 at 17:51
  • Well i am going to be using the selection to know the start column and end column as well as knowing the row. So then i can construct a Sign Page On the Pages i need to. And that selection will be used as the base point to build off. – Jacob Lenertz Dec 20 '17 at 17:52
  • 2
    @JacobLenertz I expect this code to throw an error if the `Selection` isn't at least 6 columns wide. – Mathieu Guindon Dec 20 '17 at 17:55
  • @Mat'sMug - true, but `Range.row` is a perfectly good reason not to use it. The OCD inside me is rebelling. But concerning `VBA` and `Excel Object Module` you are right, it is better to be edited. – Vityata Dec 20 '17 at 17:59
  • 1
    @Vityata FWIW every single `Property Let` and `Property Set` procedure I write takes a `ByVal value As Whatever` parameter. I've gotten used to see `Range.Value` as `Range.value`, and live a happy life ;-) – Mathieu Guindon Dec 20 '17 at 18:01
  • @Mat'sMug - with `.Value` and `.value` I can live as well. But `.row` is too much for me. And I am not the only one - https://stackoverflow.com/questions/27570703/why-row-changes-to-row-in-excel-vba – Vityata Dec 20 '17 at 18:07