excel - This command cannot be used on multiple sections when copying discontinuous range -

 while columncount <= lastcolumn     if not isempty(mailsheet.cells(2, columncount))         lastrow = mailsheet.cells(rows.count, columncount).end(xlup).row         set rng1 = mailsheet.range(mailsheet.cells(1, columncount), mailsheet.cells(lastrow, columncount))            if rng nothing              set rng = rng1         else             set rng = union(rng, rng1)         end if       end if     columncount = columncount + 1 loop  rng.copy worksheets("sheet1").pastespecial xlpastevalues 

hello, have sheet 45 columns, of have headers. of these columns, ~5 have 2 or 3 values below headers.

my code above creates discontinuous range (variable "rng"). want figure out way create "continuous range"

i thought using pastespecial command work, seen in other stackoverflow questions (link: copying discontinuous range 1 sheet another). however, in case, code not run beyond

rng.copy

line, , returns error: command cannot used on multiple sections

ultimately, email rng, have module already. however, needs continuous, either figure out way reformat through copy/pastespecial or create new range.

any tips appreciated!

the best way use range.currentregion. if a1 first cell, go immediate window , type

?range("a1").currentregion.address 

and see if gives range want. if have stuff adjacent stuff want, currentregion big. in case, find column rows, , use determine how many rows contiguous range has.

sub copyrange()      dim long     dim lmax long      'i assume you're setting     const columncount long = 3      'loop through columns , find longest 1     = 1 columncount         if sheet1.cells(sheet1.rows.count, i).end(xlup).row > lmax             lmax = sheet1.cells(sheet1.rows.count, i).end(xlup).row         end if     next      'if starting cell a1, resize , copy     sheet1.cells(1, 1).resize(lmax, columncount).copy      'paste somewhere     sheet1.cells(10, 10).pastespecial xlvalues  end sub