Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled row

**looney** · 06-12-2013, 08:58 AM

Hi I try to make an selection in a macro, but it seems to make an selection to the end of the column. For example... the cells are now filled till B144 but after using Range(Selection, Selection.End(xlDown)).Select it goes to B65536. Same happens when I do this manually with Ctrl End Down keys.

How can I fix this?

Is there an other way to make this selection?

**arlu1201** · 06-12-2013, 09:11 AM

Any reason why you want to do the selection?

If you want to copy (maybe) the data, you do not need to select. If you want to copy from row 144 (or even a row which changes everytime), do this

dim lrow as long
lrow = worksheets("Sheet1").range("A" & rows.count).end(xlup).row
worksheets("Sheet1").range("A2:C" & lrow).copy

**looney** · 06-12-2013, 09:15 AM

I want to use the next sub on it...

Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "     ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "    ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "   ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "  ", " ")
        Next
    On Error GoTo 0
End Sub

I just noticed that the blank cells all have some spaces in it... what is causing the selection till 65536...

**arlu1201** · 06-12-2013, 09:31 AM

Try this

Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
    Dim MyCell As Range
    Dim lrow As Long
    
    lrow = Range("A" & Rows.Count).End(xlUp).Row
    On Error Resume Next
        For Each MyCell In Range("A2:P" & lrow).SpecialCells(xlCellTypeConstants, 23).Cells
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "     ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "    ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "   ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "  ", " ")
        Next
    On Error GoTo 0
End Sub

**looney** · 06-12-2013, 09:57 AM

I am not sure if that part of coding will work... and I rather leave it as it works exactly on the part that is selected.

I figured out what is causing the spaces on all the other cells.

It is the next part of code:

    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[3],"" "",RC[2])"
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0)), Type:=xlFillDefault

This part of coding is autofilling the formula all the way till row 65536. If I can make sure it does not go any further then the last filled cell of column C it would help me out a lot.

**looney** · 06-12-2013, 10:23 AM

I found some coding and tried to modify it for myself...

but I am getting the a 1004 error. obviously I am big noob and got some parameters wrong.

 Range("B2", Cells(Rows.Count, "C").End(xlUp)).Offset(, 1).FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[3],"" "",RC[2]))"

Does anybody know how to alter it to my situation?

**Norie** · 06-12-2013, 10:27 AM

Try this.

 Range("B2", Cells(Rows.Count, "C").End(xlUp).Offset(, -1)).FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[3],"" "",RC[2]))"

**JOHN H. DAVIS** · 06-12-2013, 10:50 AM

Maybe:

Range(Range("B2"), Range("C" & Rows.Count).End(3)(2)).FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[3],"" "",RC[2])"

**looney** · 06-12-2013, 10:31 AM

Still an error 1004 :-/

**arlu1201** · 06-12-2013, 10:31 AM

Try

 Dim lrow as long
lrow = range("C" & rows.count).end(xlup).row
Range("B2:B" & lrow).FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[3],"" "",RC[2]))"

**looney** · 06-12-2013, 10:51 AM

Thanks you all!

It seemed there was a ")" to many at the end of the formula causing the 1004 error..
I am using Arlu's version now. but I think Norie's would do as well.

Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled row

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled row

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Re: Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled ro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions