Selecting Certain Columns & Rows

[APElliott]

Hello,

I think this is a simple one for most of you.

I have a sheet of formulae that summarises from another sheet. It sorts the information into a particular order. The majority of the cell values are 0 and these are sorted to the bottom of the worksheet.

OK.

The full sheet range is A1:O571, but lets say the data only goes down to row 103. Bearing in mind that it isn't always going to be row 103 how do I Copy select columns A,B,D & J for these rows?

Help,

Cheers

Andrew

 

[shrubble]

Declare a variable:

Dim pageCount as Integer
pageCount = 0

Write a function that keys on contiguous rows, and iterates until it finds an empty one, where the data stops: (code not exact)

do while not(sheet1.cells(pageCount + 1,1) = "&quot
pageCount = pageCount + 1
end while

or something similiar, you get the idea. The important thing is that pageCount ends up equal to the total rows of data on the page.

Now, write a loop that copies certain data from point A to point B until the lsat row of data is reached (pageCount):

Dim counter as Integer
counter = 0

Dim i as Integer '<-- row, column, whatever
Dim j as Integer '<-- same here

do while (counter <= pageCount)
sheet1.cells(i,j) = sheet2.cells(i,j)
i = i + 1 '<-- or whatever!
j = j + 1 '<-- or whatever!
End While

On my site, under skills, download the first VBA spreadsheet, there is a perfect example of this in there somewhere.

Hope this helps

-Shrubble

http://www.ishrubble.com