We help IT Professionals succeed at work.

Modify VBA Offset and Resize

Medium Priority
767 Views
Last Modified: 2012-08-13
I need help adjusting how rows get inserted and deleted with the macro in the attached file.  If you click the "Click to Insert" button and enter a positive number.  All formulas and formatting in the row above current position of cursor will copied.

If your cursor is positioned in row 4 for example, and you type "-2" in the dialog box, the macro will delete rows 4 and 5 from all select worksheets.  I would like to modify this to so that rows 3 and 4 would be deleted instead.  I'm fairly certain that this line of the code holds the key to making this happen.
ActiveSheet.Rows(sel).Resize(Abs(rng), 1).EntireRow.Select

Open in new window


However I have not had any luck properly applying it.  If someone could provide a recommendation and explain these two lines, I would be grateful.  Thanks!
ws.Cells(sel - 1, 1).Resize(rng + 1, 1).EntireRow.FillDown

Open in new window

ActiveSheet.Rows(sel).Resize(Abs(rng), 1).EntireRow.Select

Open in new window


P.S. I also attached the excel file that contains the complete code.  This should give you a better understanding to question.






Example-Code.xlsm
Comment
Watch Question

I think you're right and that this should do it

ActiveSheet.Rows(sel).Offset(rng + 1).Resize(Abs(rng), 1).EntireRow.Select

Open in new window

CERTIFIED EXPERT

Commented:
ws.Cells(sel - 1, 1) ' this is telling it to select the row right above it (-1)

.Resize(rng + 1, 1) 'this is telling it to change the selection to move down 1 row to include the new row I'd assume.

.EntireRow.FillDown,  'this is it telling it to copy the information down to the new row just selected.

Open in new window


ActiveSheet.Rows(sel)' This seems to select the current row

.Resize(Abs(rng), 1) ' This seems to resize it in the same way but is using a variable that is the same row so, um no real resize?

.EntireRow.Select    ' This selects the entire row

Open in new window


The other lines basically fill the whole range on the basis of the top cell. The size of the range is the number of rows entered in the input box

Author

Commented:
I commented out the original line of code I had and added your suggested code (see below), but it still does not accomplish what I need.

        If rng < 0 Then
            ActiveSheet.Rows(sel).Offset(rng + 1).Resize(Abs(rng), 1).EntireRow.Select
            'ActiveSheet.Rows(sel).Resize(Abs(rng), 1).EntireRow.Select
            Selection.Delete
        End If

In the attached file, if I place my cursor on cell A4, run the macro, and input "-3" in the dialog box, I would expect row 4, 5, 6 to get deleted.  But that's not the case.  What am I missing?


Example-Code-REVISED.xlsm
I was going the wrong way, but your original line seems to do the trick?

ActiveSheet.Rows(sel).Resize(Abs(rng), 1).EntireRow.Select

Author

Commented:
If you run this in my attached sheet, you'll see this did not do the trick.
If you run this in your attached sheet, you'll see this did do the trick.

That will be my last word on this.
CERTIFIED EXPERT

Commented:
hi Kp,

I may be misunderstanding your needs but have you changed your requirements from the start of the thread?

The first post (www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27426194.html#27426194 ) states:  
"If your cursor is positioned in row 4 for example, and you type "-2" in the dialog box, the macro will delete rows 4 and 5 from all select worksheets.  I would like to modify this to so that rows 3 and 4 would be deleted instead."

and then in ( www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27426194.html#37066220 ) you state "In the attached file, if I place my cursor on cell A4, run the macro, and input "-3" in the dialog box, I would expect row 4, 5, 6 to get deleted."

The first quote suggests you want the rows above the active row deleted, whereas the second quote seems to the rows below the active cell & including the activecell. Which one is correct?


I tried to make it clearer for myself by renaming the variables (in the code from the first post) and came up with the below. I was messing around with different approaches while watching telly & added some extra error handling. However, I am conscious that this code may not be efficient (as the original) because I loop through each of the sheets repeating every action on the sheets in the array & don't perform any actions on the ws array in a single go.

Option Explicit

Public Sub process_Alternative()
Dim MyList As String
Dim wsArr As Variant
Dim ws As Worksheet
Dim NumOfRowsStr As String
Dim NumOfRowsToChange As Long
Dim curRowNum As Long
Dim IsNumOfRowsToChangeValid As Boolean
Dim ErrorMsg As String
    Stop
    curRowNum = ActiveCell.Row

    'A loop to ensure a valid number is entered
    Do
        '-- determine required number of rows
        'the use of "Type:=1" in the "Application.InputBox" means that you can ensure a number is entered
        NumOfRowsStr = Application.InputBox(Prompt:="Enter number of rows required." & vbCrLf & vbCrLf & "All formulas and formatting in row above current position of cursor will be copied.", Title:="REQUEST FOR INPUT...", Default:=ErrorMsg, Type:=1)

        'The sub will stop if [cancel] or the [white cross in the red square] is pressed, or "0" is entered.
        If Not CBool(NumOfRowsStr) Then Exit Sub
        NumOfRowsToChange = CLng(NumOfRowsStr)

        'extra error checking on the validity of the user's input
        Select Case NumOfRowsToChange
            Case Is < 0
                IsNumOfRowsToChangeValid = (curRowNum - Abs(NumOfRowsToChange)) > 0
            Case Is > 0
                IsNumOfRowsToChangeValid = (ActiveSheet.Rows.Count - curRowNum) > NumOfRowsToChange
        End Select
        If Not IsNumOfRowsToChangeValid Then ErrorMsg = "Please enter a number that is valid based on the current selected row..."
    Loop While Not IsNumOfRowsToChangeValid

    '-- generate list of worksheets to process
    MyList = "Summary,Details"
    For Each ws In ThisWorkbook.Worksheets
        If LCase(ws.Name) Like "zone*" Then
            MyList = MyList & IIf(Len(MyList) = 0, vbNullString, ",") & ws.Name
        End If
    Next ws
    wsArr = Split(MyList, ",")
    Stop
    'loop through each of the appropriate worksheets
    For Each ws In Worksheets(wsArr)
        With ws
            Select Case NumOfRowsToChange
                Case Is > 0
                    '-- perform the insert on the worksheet
                    .Rows(curRowNum).Resize(NumOfRowsToChange, 1).EntireRow.Insert
                    .Cells(curRowNum - 1, 1).Resize(NumOfRowsToChange + 1, 1).EntireRow.FillDown
                Case Is < 0
                    'an attempt to ensure...
                    '"If your cursor is positioned in row 4 for example, and you type "-2" in the dialog box,
                    'the macro will delete rows 4 and 5 from all select worksheets.
                    'I would like to modify this to so that rows 3 and 4 would be deleted instead."
                    .Rows(curRowNum - Abs(NumOfRowsToChange)).Resize(Abs(NumOfRowsToChange), 1).EntireRow.Delete
            End Select
        End With
    Next ws
    MsgBox "done"
End Sub

Open in new window


hth
Rob

Author

Commented:
Hi Rob and Stephen, I'm sorry for the confusion on this.  My requirements remain the same.  Both were two different examples to accomplish the same thing.  Let me clarify:

"If your cursor is positioned in row 4 for example, and you type "-2" in the dialog box, the macro will delete rows 4 and 5 from all select worksheets.  I would like to modify this to so that rows 3 and 4 would be deleted instead."
"In the attached file, if I place my cursor on cell A4, run the macro, and input "-3" in the dialog box, I would expect row 4, 5, 6 to get deleted."

If I were to select any cell in row 4 (e.g. A4, B4, D4, F4, or etc.), and run the "process macro", I will receive a dialog box.  In the dialog box I can enter positive or negative values.  Negative values pertain to delete and positive values pertain to inserting rows.  If I input "-2", it means to delete 2 rows.  If I input -3, it means to delete 3 rows.  If I input -5, it means to delete 5 rows.

When deleting rows, I would like to change the starting point to the row above the one I currently have a cell selected in.  So if cell A4 was selected and "-2" was inputted in the dialog box, then rows 3 and 4 would be deleted.  If I inputted "-3", then rows 3, 4, and 5 would be deleted.  If I inputted "-5", then rows 3, 4, 5, 6, and 7 would be deleted.

I hope this adds more clarity.  If not, I won't pursue this thread any further.  I'll split the points between you both and close the questions.  Below is the original code that I need to modify.
Public Sub process()
Dim MyList As String
Dim arr As Variant
Dim arrZones As Variant
Dim ws As Worksheet
Dim strRng As String
Dim rng As Integer
Dim start As Range
Dim sel As Integer
Dim n As Integer
    
    
    '-- determine required number of rows
    strRng = InputBox("Enter number of rows required." & vbCrLf & vbCrLf & "All formulas and formatting in row above current position of cursor will be copied.")
    If strRng = "" Then Exit Sub
    
    If IsNumeric(strRng) Then
        rng = CInt(strRng)
    Else
        Exit Sub
    End If
    
    '-- generate list of worksheets to process
    MyList = "Summary,Details"
    For Each ws In ThisWorkbook.Worksheets
        If LCase(ws.Name) Like "zone*" Then
            MyList = MyList & IIf(Len(MyList) = 0, "", ",") & ws.Name
        End If
    Next
    arr = Split(MyList, ",")
    
    ' first add the rows to the zones sheets
    sel = Selection.Row
    Set start = Selection
    
    ' select all required sheets as a group, then do the insert
    ThisWorkbook.Worksheets(arr).Select
    
    
    If rng > 0 Then
        ActiveSheet.Rows(sel).Resize(rng, 1).EntireRow.Select
        Selection.Insert
        
        ActiveSheet.Select
        '-- perform the insert on selected worksheets
        For Each ws In Worksheets(arr)
            '-- insert formulae
            ws.Cells(sel - 1, 1).Resize(rng + 1, 1).EntireRow.FillDown
        Next ws
    Else
        If rng < 0 Then
        
            ActiveSheet.Rows(sel).Resize(Abs(rng), 1).EntireRow.Select
            Selection.Delete
        End If
    End If
    
End Sub

Open in new window

That's quite different from what you had previously stated.
ActiveSheet.Rows(sel).Offset(-1).Resize(Abs(rng), 1).EntireRow.Select

Open in new window

Author

Commented:
Stephen, thanks for not giving up on me.  This is precisely what I needed.  Thank you, thank you. ;-)
CERTIFIED EXPERT

Commented:
Well...
Stephen's over 30 minutes ahead of me but to repeat the principle of his solution in terms of my previous code, change line 57 from...

.Rows(curRowNum - Abs(NumOfRowsToChange)).Resize(Abs(NumOfRowsToChange), 1).EntireRow.Delete

Open in new window


to
.Rows(curRowNum - 1).Resize(Abs(NumOfRowsToChange), 1).EntireRow.Delete

Open in new window


hth
Rob