VBA Method Range of Object _Global Failed - loop

Question

I've got a problem with a very simple method, range.

I know I've got to specify the sheet when I'm working across sheets, but I just don't know how to do it nicely :'-( could anyone explain how this works?

and obviously the issue took place here!

Range(Selection, Range(Selection).End(xlToLeft).End(xlToRight)).Delete shift:=xlUp

Thank you so much in advance! I haven't got any problem with other part of the code.

Many Thanks in advance! :-)

Sub NarrowingDowning()

    Dim LoopCounter, i, j As Long
    LoopCounter = Range("B3", Range("b3").End(xlDown)).Cells.Count

    For j = 2 To 4

    '**** this section below is sorting out data: NO ERRORS here ****' 
    Worksheets(j).Sort.SortFields.Clear
    Worksheets(j).Sort.SortFields.Add Key:=Range("G8") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(j).Sort
        .SetRange Range("A3", Range("A3").End(xlToRight).End(xlDown))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    ActiveSheet.Range("G3").Select
    For i = 1 To LoopCounter
        ActiveSheet.Range("g3").Offset(i, 0).Select
        If Selection.Value > 0.85 Then
            Selection.Select
        ElseIf Selection.Value < 0.85 Then
            Selection.Offset(0, 1).Select
            Range(Selection, Range(Selection).End(xlToLeft).End(xlToRight)).Delete shift:=xlUp
        End If

        If Selection.Value = "" Then Exit For
    Next i

Next j
End Sub

I need to make it clear. sorry guys. I've got a table, A2:H300, and there're other tables away two columns next to it which shouldn't be affected. and when a certain condition is satisfied (i.e. G(i).value <0.85) then I want to delete only the row within this particular range. So for example, if G(6).value is 0.2, which satisfies the condition, then the 6th row within the range A6:H6 will be deleted, not affecting other tables :)

Sorry for getting all you guys confused.

what do you expect Range(Selection, Range(Selection).End(xlToLeft).End(xlToRight)).Delete shift:=xlUp TO DO?? — Gary's Student
– Gary's Student, Commented Aug 8, 2014 at 15:26
Range(Selection) doesn't particularly make any sense to me because Selection is already a Range object. If I am correct in assuming you are trying to delete the row(s) of the selected range, there's a perfectly good answer below. — Soulfire
– Soulfire, Commented Aug 8, 2014 at 15:40
I've got a table, A2:H300, and there're other tables away two columns next to it which shouldn't be affected. and when a certain condition is satisfied (i.e. selection.value <0.85) then I want to delete only the row within this particular range. — Vivian
– Vivian, Commented Aug 8, 2014 at 16:18

Dave.Gugg · Accepted Answer · 2014-08-08 16:25:35Z

2

How about just:

Selection.EntireRow.Delete shift:=xlup

Edit:

Since you don't want to delete the entire row, you can capture the immediate range of cells that have something in them like this:

Range(Selection.End(xlToLeft), Selection.End(xlToRight)).Delete shift:=xlUp

edited Aug 8, 2014 at 16:25

answered Aug 8, 2014 at 15:39

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vivian Over a year ago

>> Dear Dave, I shouldn't delete the entire row because the thing I want to delete is within a range, and if I delete the entire row then this will end up with deleting other ranges as well.

Dave.Gugg Over a year ago

Edited so entire row will not be deleted.

Collectives™ on Stack Overflow

VBA Method Range of Object _Global Failed - loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related