Selection.ClearContents makes macro halt???

[Fan924]

What would make this macro stop after "Selection.ClearContents" It is
random. It makes it to the end most of the time. Very unreliable.
excel97


Sub ModuleImport()
Dim FileNameWithPath As Variant
Dim FNum As Variant
Dim SeparatorSymbol As String
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
SeparatorSymbol = "!"
'On Error Resume Next
Sheets("DataModule").Select
SaveColNdx = 1
RowNdx = 3

'>>>>>>>>>>>>>>>>>>>> Get File Name <<<<<<<<<<<<<<<<<<<<
FileNameWithPath = Application.GetOpenFilename(FileFilter:="Module
Files (*.dme),*.dme")
If FileNameWithPath = False Then
Exit Sub
End If
'>>>>>>>>>>>>>>>>>>>> Clear old Data <<<<<<<<<<<<<<<<<<<<
Range("A3:N300").Select
Selection.ClearContents
Selection.NumberFormat = "@"
Range("A1").Select
msgbox "Made It"
end sub

 

[OssieMac]

I assume that you have left some of the code out of the post.

I have no idea why it is not working. However, it is almost never necessary
to select ranges to manipulate data. You can simply refererence the range.
Try the following and see if it makes a difference.

Replace the following code
Range("A3:N300").Select
Selection.ClearContents
Selection.NumberFormat = "@"

with this code
Range("A3:N300").ClearContents
Range("A3:N300").NumberFormat = "@"

Note that your code is applied to the currently active sheet.

 

[Fan924]

Range("A3:N300").NumberFormat = "@"
gives "NumberFormat method of range class failed"

 

[joel]

The code would fail if the selected sheet in the workbook wasn't
worksheet. A workbook contains charts and pictures which doesn't have
range of cells. The selected sheet in the open workbook could b
protected. There are userform sheets in workbooks that may not hav
300 rows or columns out to column N.

I agree with Ossi and also recommend alway specifying the sheet nam
besides avoiding the selection method

Set sourcesht = sheets("sheet1")
With Sourcesht
LastRow = .Range("A" & Rows.count).End(Xlup).row
LastCol = .Cells(1,Columns.count).End(Xltoleft).column

if LastRow < 3 then
msgbox("There is not Data on Worksheet - Exiting Macro ")
exit sub
end if
Set DataRange = .Range(.range("A3"),cells(LastRow,LastCol)

DataRange.ClearContents
DataRange.NumberFormat = "@"
end wit

 

[OssieMac]

In my previous post I indicated that you had left out some of the code in
your post. Is it running from a Userform? There are some conditions when code
runs from a Userform if it comes across an error then the code appears to
simply abort instead of stopping on the error.

My thoughts are that for some reason your original problem was because the
range could not be selected. In my previous answer I should have been more
specific and said to include the sheet name in the code instead of just
saying it will run on the active sheet.

The sheet name is included with both of the following examples although the
latter is the more professional. Replace "Sheet1" with your sheet name.

Sheets("Sheet1").Range("A3:N300").ClearContents
Sheets("Sheet1").Range("A3:N300").NumberFormat = "@"

or

'Note the dot in from of Range which ties it to Sheets("Sheet1")
With Sheets("Sheet1")
.Range("A3:N300").ClearContents
.Range("A3:N300").NumberFormat = "@"
End With

Using the above methods neither the sheet nor the range need to be selected.