FAO: visual basic guru's.

The Northern Monkey

I've created a macro in visual basic that copies everything in an excel sheet and pastes it in a newly created sheet "sheet1" and it does this for every original sheet in the workbook.

Now, is there a code i can use that will mean it only copies down to the last populated row?


Cheers!

whyamihere

Pseudo-code time!

i=1
while "Ai" <> 0: //Look at the contents of the row number i, if it's not empty, enter the loop
      Copy stuff
      i = i + 1 //Go to new row
end while

As for implementing that in VB, not a damn clue. If you've already got as far as you have though, you should know the syntax for it.

cat_with_no_tail

I've tried it a few times and never been able to figure it out. Various excel forums and google havent helped much either.

If you do figure it out, let me know. :P

The Northern Monkey

shall do. proper pissing me off.. I've had to take an educated guess on how far down a sheet it copies data from

created a macro in visual basic thats so huge i've had to split it into 2 separate macros... it does run over 300 sheets though!

whyamihere

Does it *have* to be in VB? If you could give me a few days, I can have a crack at knocking something up in C++. It'll probably require a version of the spreadsheet being saved in plain text, as I have no idea how to implement a way of reading from an Excel file, but it'd be better than guessing...

The Northern Monkey

I know nothing of c++ unfortunately... I actually don't know what it is or where/how you use it!

I only started using VB properly a week ago and i only use it in excel to create big macros!

I'm sort of teaching myself VB as I need to use lots of macro's in work and no one in the office knows what a macro is!

whyamihere

C++ is just another programming language, and is the one where I'm most familiar with the syntax, being the one I use at uni. VB will be able to do what you want to do, but I don't know how to implement it (it's been a long time since I used VB), and don't want to relearn it.

The Northern Monkey

no worries, cheers anyways!

I'l keep looking on the net, i've found a few codes but none of them have worked how I wanted.

whyamihere

Don't look for code to do exactly what you want it to, you'll fail. Find a code snippet which tells you how to read in the data from the row (you should already know how to do this from copying the data across). Find out what's stored in an empty row by reading one in, and compare the cell you're looking at to the empty cell. First time it hits an empty cell, it can stop.

The Northern Monkey

hmm ok I think I know what you mean. I'll give this a go tomorrow when I get in work and see what I can come up with.

lemoncurd

This will do it, replace the Source and Target sections with your sheet names.

Sub CopyCellContents()
    Dim x As Integer
    x = 1

    While Worksheets("Source").Cells(x, 1).Value <> ""
        Worksheets("Target").Cells(x, 1) = Worksheets("Source").Cells(x, 1).Value
        x = x + 1
    Wend

End Sub

The Northern Monkey

hmmm cheers!

I'll give that a go and see if I can get it to work how I want!

Seems to make sense though.

BorisSpencer

It's crude but this will work:

ActiveSheet.Select
ActiveSheet.Cells(65000, 1).Select
Selection.End(xlUp).Select
intRows = ActiveCell.Row
ActiveSheet.Cells(1, 200).Select
Selection.End(xlToLeft).Select
intCols = ActiveCell.Column
MsgBox "Rows=" & intRows & ", Cols=" & intCols

Assuming data is in column 1 and row 1, adjust as applicable and use starting values that are beyond where the data may end.

BorisSpencer

Better:

intRows = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
intCols = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
MsgBox "Rows=" & intRows & ", Cols=" & intCols

No assumptions.

The Northern Monkey

I think I need to show you guys my original code so you can help me input this properly, i'll post it up tomorrow when i get in work.

Cheers for the help though!