Selecting Cells in column for Deletion

[Rocky0201]

Hi,

I am trying to select a number of cells in a column that may not be sequential in order to delete these cells and move cells up 1. My goal would be much like the following manual example:

Click on cell G4
Hold Ctrl key
Click on cell G6
Right click
Select delete
Select Shift Cells Up

I ran through a loop that built an array of the row number of the cells that I wish to delete but I'm lost on how to seclect just those cells in the array to perform the delete and shift cells up.

This is my code to indentify the rows of the cells in column G to delete"

Erase CheckBoxDelArray()
ReDim CheckBoxDelArray(1 To 14)

i = 1
ii = 4

For i = 1 To 14

If Me.Controls("CheckBox" & i).Value = True Then

CheckBoxDelArray(ii) = CheckBoxDelArray(ii) + 1

End If

ii = ii + 1

Next i

Using Watch, I determined the correct row numbers were in the array.

Any help would be great.

Thanks.

 

[wigi]

Here is an example code that is fully worked out, and does what we expect it to do: it deletes the even rows. Amend to your specific situation:

Sub ddd()

    Dim CheckBoxDelArray(1 To 14)

    i = 1: ii = 4
    For i = 1 To 14

        If i Mod 2 Then CheckBoxDelArray(i) = ii & ":" & ii
        
        ii = ii + 1

    Next i
    
    s = Left(Replace(Join(CheckBoxDelArray, ","), ",,", ","), Len(Replace(Join(CheckBoxDelArray, ","), ",,", ",")) - 1)
    
    Range(s).Delete

End Sub

 

[Rocky0201]

Thanks Wigi...

Not sure how the code works in my case.

I have a userform with 14 checkboxes. Each checkbox corresponse to a cell in column G starting at G4 through G17. If ckeckbox1 and checkbox 3 are selected, I need to delete cells G4 and G6 at the same time and shift cells up 1.

I created a routine that would loop through the checkboxes and when i found a checkbox checked, i would delete the cell shifing the cells up 1. The problem is, when I encounter the first cell to delete and then delete it, the next cell that i need to delete is no longer the correct cell because all cells shifted up by 1.

Hopefully you can help.

Thanks again...

 

[wigi]

As you have probably understood from studying my code, the delete process is all at once. So your last problem (rows shifting and row numbers being incorrect), is a non-issue in my code.

My code makes a loop and tracks row numbers (full rows) in an array. If you understand this, it will be easy to incorporate it into your loop over checkboxes. Only the loop needs to change.

Wigi

 

[Rocky0201]

Many thanks Wigi.

I'll give this a try...

 

[Rocky0201]

Hi wigi...

The loop I understand. It's the rest of the routine I'm having a bit of difficulty understanding.

I see were my row numbers are being setup in my array. Although the next statement (s = ) is a little out of my realm of understanding, It appears that when all is said and done (range(s).delete), I will be deleting the entire rows defined in the array. I am looking to delete cells only at he same time in column G i.e., G4 and G6 or G5, G7, G8 and G10 at he same time.

Thanks for your help and your patients...

 

[wigi]

Code updated based on the previous explanation.

Sub ddd()

    Dim CheckBoxDelArray(1 To 14)

    i = 1: ii = 4
    For i = 1 To 14

        If i Mod 2 Then CheckBoxDelArray(i) = "G" & ii
        
        ii = ii + 1

    Next i
    
    r = Replace(Join(CheckBoxDelArray, ","), ",,", ",")
    s = Left(r, Len(r) - 1)
    
    Range(s).Delete xlShiftUp

End Sub

Also, for your understanding, s is now replaced so that you see what it does.

 

[Rocky0201]

Hi wigi.

I used your routine. you are correct, I can see what is being setup in r and s.

I am getting a 1004 runtime error on the range statement:

Range(s).Delete xlShiftUp

Error description = Method 'Range" of object'_Global' failed

Here is my code in this routime:

'OK Button Pressed in Userform6
Private Sub CommandButton1_Click()
Dim CheckBoxDelArray(1 To 14)

i = 1
ii = 4

For i = 1 To 14
If Me.Controls("CheckBox" & i).Value = True Then
CheckBoxDelArray(i) = "G" & ii

End If

ii = ii + 1
Next i

r = Replace(Join(CheckBoxDelArray, ","), ",,", ",")
s = Left(r, Len(r) - 1)

Range(s).Delete xlShiftUp
Unload Me
End Sub

 

[wigi]

Hi

Can you do a Debug.Print just above the line

Range(s).Delete xlShiftUp

Then after running the code, in VBA, hit Ctrl-G and look what the output is. Copy it to the forum please.

Wigi

 

[Rocky0201]

Perhaps I'm doing this wrong.

I placed Debug.Print on the line directly above the Range statement.

I went into my spreadsheet and double clicked the cell which causes my first userform to launch (userform 1). I then click my modify button on Userform 1 and the userform 6 is displayed. I made my check mark and hit ok. I immediately got the runtime error. I did a control G but was not allowed until I answered the runtime error pop up, I answered debug and the highlight was on the range statement. I did a control G and the Immediate widow appeared at the bottom of my VB code but was empty. I tried a number of different ways but the Immediate window is still empty.

Any suggestions would be great.