0

every time theres only one column with data in Sheet1 and I run the code below it's giving me this error message.

Runtime Error 1004; we can't do that in a merged cell 

Sheets("Sheet1").Select
Range("E6", "G6:I6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Windows("Excel.xlsm").Activate
Sheets("Sheet1").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Shilos_Dad
  • 15
  • 1
  • 5
  • 2
    Please see [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). You should start by removing every instance of `.Select` as this is a GUI and not needed on the backend – urdearboy Mar 14 '19 at 13:49
  • 2
    A solution could be to get rid of the merged cell. Do you need it? – Joost Mar 14 '19 at 14:02
  • thank you for the tips. i'll try and redo my macro without the .Select. im very much new to this – Shilos_Dad Mar 15 '19 at 05:15

1 Answers1

0

First thing to note is that you should never use .Select in Excel Vba. You really need to learn how to use With etc

Second thing to note is that you need to recreate this issue. Go and follow the macro that you recorded and find the issue there

Because your code came from a Macro Record, it is very choppy and bad. You should never select multiple cell types and paste in vba

Third thing to mention now, is that you likely wont need to "Copy+Paste" - this makes mountains of issues if you make your code long enough to run and do other things (want to copy a link in Chrome, boom, your whole script has broken)

Step 1: follow the below format to start off with your macros.

Step 2: Think about what you want your code to do, and what limitations you have

Step 3: Then ask for help - when you have a MCVE

Sub Base()

Dim wb As Workbook, ws As Worksheet, rng As Range


Set wb = ThisWorkbook
Set ws = Worksheets("Sheet1")
Set rng = 'whatever you want

    With rng
        'Here you would copy your range
    End With

    'Here you would paste/move your range

End Sub

Naming a range:

Dim rngName As String, rngB As Range

rngName = "NamedRangeExample"

Set rngB = ws2.Range("A5:C10")

wb.Names.Add Name:=rngName, RefersTo:=rng2
Badja
  • 857
  • 1
  • 8
  • 33
  • 1
    can you name a range like this? Set rngB = ws2.Range("E6" & "G6:I6") – Shilos_Dad Mar 15 '19 at 10:20
  • I've edited my answer to show how to name a range. Although I wont do everything for you, you need to find out how to make a range of multiple selections – Badja Mar 15 '19 at 10:29