CODEWITHSUNDEEP
excelvba
niko
niko

Reputation: 9393

Delete rows and insert rows in Ranges declared as Variant

I have to go through the complete A column and check if cell value length is < 6 then delete it.

I used to perform the operations on the sheet like this

 activesheet.range("A" & row_number).select
 selection.entirerow.delete

As people suggested using a variant I want to work with variants.

I have taken a set of range into a variant.

 dim var as variant 
 var=sheet1.range("A1:D1000").value

Suppose I have 20 rows whose cell value length in A column is less than 6. I have to remove these 20 rows in the variant variable, including the other corresponding columns in variant that is B, C, D. I mean var("A18:D18") should be completely removed.

I heard people say we can't delete an entry from a variant, we should take a new variant and copy only those values into the new variant. If that is the situation how do I copy one variant to another variant?

For rows whose A column cell value length is greater than 6, cell values should be converted into a standard format. I have done it using the sheet like

activesheet.cells("some cell!).value=activesheet.cells("").value

I loop through each row and it is taking some time as every time I'm knocking the sheet for values. I want to use variants now, take the complete range, perform the operation and write it back.

How to remove the complete row that we entered into variant like var("A2:D2"), then copy the var("A4:D4") value to other variant like var2("A6:D6")?

Can we also insert an entry into variant at the middle of the variant like we insert row in the sheet?

Upvotes: 0

Views: 2530

Answers (1)

brettdj
brettdj

Reputation: 55682

Something like this

  1. It takes A1:D1000 on the first worksheet (array X)
  2. Tests each column A cell to see if it is longer than 6 characters
  3. If it is greater than 6 then the entire row (4 cells in your case) is written as a new row in array Y
  4. The reduced array is written to A1 in the sexcond worksheet

  5. Added formatting of sheet1 columns to sheet2 columns

    Sub VarExample()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ActiveWorkbook.Sheets(1)
Set ws2 = ActiveWorkbook.Sheets(2)
Dim X
Dim Y
Dim lngRow As Long
Dim lngCOl As Long
Dim lngCnt As Long
'define the size of the array to be processed on sheet 1
X = ws1.Range("A1:D1000").Value2
'make the second array the same size as the first
ReDim Y(1 To UBound(X, 1), 1 To UBound(X, 2))
'Look at the first record in each row  [,1] part to see if it is longer than 6 chars
For lngRow = 1 To UBound(X, 1)
    If Len(X(lngRow, 1)) > 6 Then
     'Longer than 6 so add 1 more row to the length of the 2nd array
        lngCnt = lngCnt + 1
        'Loop through value in this row of the first array and place in the second array
        For lngCOl = 1 To UBound(X, 2)
            Y(lngCnt, lngCOl) = X(lngRow, lngCOl)
        Next lngCOl
    End If
Next
'create a range on the second sheet equal in size to the second array and dump the array to it
ws1.[a1].Resize(UBound(Y, 1), UBound(Y, 2)).Value2 = Y
'copy formatting
ws1.[a1].Resize(1, UBound(X, 2)).EntireColumn.Copy
ws2.[a1].Resize(1, UBound(X, 2)).EntireColumn.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

Upvotes: 4

Related Questions