Error 1004 When Trying To Move Cells 24 Cols To The Right

[HowdeeDoodee]

http://www.mrexcel.com/forum/showthread.php?t=323741

From the above thread I am using the following macro...

Sub MoveOver()
Dim MoveRange As Range

Application.ScreenUpdating = False
With Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(, 21))
.AutoFilter field:=1, Criteria1:="=C&P"
On Error Resume Next
Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter
End With
If Not MoveRange Is Nothing Then
MoveRange.Insert Shift:=xlToRight
End If
Application.ScreenUpdating = True
End Sub

I am getting an error 1004 message saying cells cannot be moved off of the spreadsheet. However, my last cell on the sheet is J28956.

Columns B through J contain data. Columns beyond J do not contain data.

Can someone give advice on getting the macro to work? Thank you in advance for replies.

 

[Peter_SSs]

I guess a couple of things:

1. If you press Ctl+End, can you confirm that you are taken to cell J28956?

2. Try selecting columns K:IV (assuming you are not using Excel 2007 with more columns than that) and then Edit|Clear|All
Save the workbook and try the code again.

3. I note in the other thread you said it was working fine. What has changed since then?

 

[HowdeeDoodee]

I guess a couple of things:

1. If you press Ctl+End, can you confirm that you are taken to cell J28956?

2. Try selecting columns K:IV (assuming you are not using Excel 2007 with more columns than that) and then Edit|Clear|All
Save the workbook and try the code again.

3. I note in the other thread you said it was working fine. What has changed since then?

Thank you, Peter. It is not your code it is something in the sheet. I do not know where the problem is or how to find it.

Could you help with this alternative? Could you put the code in a macro that would be executed when I hit a control key? I would need the macro to search for any cell in col B with the content "C&P" and then move the cells over to the right 24 cols. This way, perhaps I can find out where the problem cells are and what else might be causing an issue. The content of the sheet is from a digitized document 150 years old. There are all kinds of merged cells and other weirdness in the sheet I did not create. I am working on a project as a volunteer like other people I know.

 

[Peter_SSs]

What was the result of suggestions 1 and 2 that I made?

 

[Mike Blackman]

Hi Howdee Doodee,

What Range are you trying to work with with this line of code?

Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(, 21))

 

[HowdeeDoodee]

What was the result of suggestions 1 and 2 that I made?

Yes, Peter, I tried 1 and 2. Ctrl + End = J28956
I tried deleting everything K to IV saving the sheet, etc. but this had no effect on the issue.

I copied and pasted the sheet into another sheet with values only, but this also had no effect and produced the same 1004 error code.

Using ASAP, I unmerged all merged cells but this also had no effect on the issue.

The sheet has both Greek and Hebrew text in the sheet along with English text.

Thank you for the help and input.

 

[HowdeeDoodee]

Hi Howdee Doodee,

What Range are you trying to work with with this line of code?

Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(, 21))

Hi, Mikey. There are 28955 rows in col B.

 

[Mike Blackman]

Should it not be;

Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(, 21))

Excuse my ignorance if not!

 

[HowdeeDoodee]

Should it not be;

Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(, 21))

Excuse my ignorance if not!

I am getting an "expected end of statement error when I insert the suggestion. It appears we have an extra ) in the suggested new code. Can anything be changed here?

Thank you for your interest.

 

[Mike Blackman]

Hi,

Its late so I'm not 100% but I think its the last one, try;

Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(, 21)