help resizing range from entire row to only part of row

[9tanstaafl9]

Hi. I got myself in trouble because I've been using code (given nicely to me by people here) that I don't entirely understand. It has been working fine for over a year, but now my report has grown so much that selecting the range using full rows is causing me to run out of resources. I need to select the range only through column J instead.

The current code is as follows:

Range(Range("j1"), Range("j" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Copy

As to what it actually does is to select a continuous block of cells (it selects the entire rows though I only need A to J) out of a much longer list, that I then copy and paste into the middle of a list on a different sheet.

If I was smart enough I could just resize it to only select/copy through J, but I can't figure out how. I haven't been doing VBA for a while, and i'm out of practice.

Any help would be appreciated, it doesn't have to be pretty or fast.

Jennifer

 

[patel45]

Range("a1:J1").Copy
but it's better you attach some more code to understand
</pre>

 

[EvR]

Try:

Range(Range("A1"), Range("j" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeFormulas, 16).Copy

all the errors out of formulas in the range A1 to J..xx

remark: you could run into problems in your code (depending on xl-version) when you've got more then 8192 ranges..

 

[Rick Rothstein]

Maybe this...

Intersect(Range("J1").Resize(Cells(Rows.Count, "J").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, 16).EntireRow, Columns("A:J")).Copy

 

[EvR]

As to what it actually does is to select a continuous block of cells..

after reading this the second time,
I'd prefer Rick's example

 

[9tanstaafl9]

Sorry, I wasn't clear before. It's not selecting just the one row, it's selecting a different number rows each time, the number varies each time and I don't know ahead of time what it will be. I don't understand the code enough to know HOW it is selecting the range from within the list that is there.

If you were looking at Excel, the table looks like a bunch of numbers in columns A-J, but instead of numbers in the column J from row 97 to the bottom, you see a #NA. So it looks like the code is finding the #N/A's, and selecting just those rows (which is what I want) to copy. They are sorted to be at the bottom of the list.

The full code is:

Private Sub FindDuplicates()

    Range(Range("LookupTop"), Range("LookupTop").End(xlDown)).Copy
    
    'pastes currently used costcodes/types/phases combination
    Range("H1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    'creates concatination range to match for new combinations after refresh
    Range("g1").FormulaArray = "=B1&""#""&C1&""#""&A1"
    Range(Range("c1"), Range("c" & Rows.Count).End(xlUp)).Offset(0, 4).FillDown
    Range(Range("h1"), Range("h" & Rows.Count).End(xlUp)).Name = "OLDCODES"
        
    'creates matching formula
    Range("j1").Formula = "=MATCH($G1,OLDCODES,0)"
    Range(Range("c1"), Range("c" & Rows.Count).End(xlUp)).Offset(0, 7).FillDown
    ActiveSheet.Calculate 'must remain
    Range(Range("a1"), Range("a" & Rows.Count).End(xlUp)).EntireRow.Sort Key1:=Range("J1")
    
   On Error GoTo Error_Exception
    Range(Range("j1"), Range("j" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Copy
Error_Exception:

    Sheets("Projected Costs").Select
    'alter when adding rows to top (can't use range)
    Rows("16:16").Insert Shift:=xlDown
    Application.CutCopyMode = False
    'alter when adding rows to top (can't use range)
    If Range("c16") = "" Then
        Rows("16:16").Delete Shift:=xlUp 'alter when adding rows to top
    End If

End Sub

 

[Rick Rothstein]

Sorry, I wasn't clear before. It's not selecting just the one row, it's selecting a different number rows each time, the number varies each time and I don't know ahead of time what it will be. I don't understand the code enough to know HOW it is selecting the range from within the list that is there.

If you were looking at Excel, the table looks like a bunch of numbers in columns A-J, but instead of numbers in the column J from row 97 to the bottom, you see a #NA. So it looks like the code is finding the #N/A's, and selecting just those rows (which is what I want) to copy. They are sorted to be at the bottom of the list.

The full code is:

Private Sub FindDuplicates()

    Range(Range("LookupTop"), Range("LookupTop").End(xlDown)).Copy
    
    'pastes currently used costcodes/types/phases combination
    Range("H1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    'creates concatination range to match for new combinations after refresh
    Range("g1").FormulaArray = "=B1&""#""&C1&""#""&A1"
    Range(Range("c1"), Range("c" & Rows.Count).End(xlUp)).Offset(0, 4).FillDown
    Range(Range("h1"), Range("h" & Rows.Count).End(xlUp)).Name = "OLDCODES"
        
    'creates matching formula
    Range("j1").Formula = "=MATCH($G1,OLDCODES,0)"
    Range(Range("c1"), Range("c" & Rows.Count).End(xlUp)).Offset(0, 7).FillDown
    ActiveSheet.Calculate 'must remain
    Range(Range("a1"), Range("a" & Rows.Count).End(xlUp)).EntireRow.Sort Key1:=Range("J1")
    
   On Error GoTo Error_Exception
    Range(Range("j1"), Range("j" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Copy
Error_Exception:

    Sheets("Projected Costs").Select
    'alter when adding rows to top (can't use range)
    Rows("16:16").Insert Shift:=xlDown
    Application.CutCopyMode = False
    'alter when adding rows to top (can't use range)
    If Range("c16") = "" Then
        Rows("16:16").Delete Shift:=xlUp 'alter when adding rows to top
    End If

End Sub

I am not sure who you are replying to, but I think the code line I posted will do what you want (once you give it a destination to copy to)... and it does not require you to sort the errors to the bottom of the list beforehand.

 

[9tanstaafl9]

That worked perfectl! Thank you!!

Thanks to everyone, this one worked. I aplogize for replying out of order, I made my long winded reply before seeing this one. No other answers needed.

 

[9tanstaafl9]

It does, thanks. I didn't see your reply earlier, I think it was written while I was replying to an earlier reply which wasn't what i needed. Thank you so much!

 

[9tanstaafl9]

Maybe this...

Intersect(Range("J1").Resize(Cells(Rows.Count, "J").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, 16).EntireRow, Columns("A:J")).Copy

Sorry I AM an idiot apparently. I see the "reply with quote" option. This is the one that worked. I just saw that my earlier replies didn't show who I was replying too.