How to fetch a whole row at a time in vba macro

[java_programmer]

I am writing a macro, which will take an excel sheet as input, process the data and after processing the data, will create a new excel sheet with the processed data.

In the input excel sheet, there will be some blank row, which I need to be ignored. But i can't get how to fetch the whole row at a instance.

The input excel sheet will be as img1 attached -

when there will be a new currency, the previous set will be subtotalled and a blank row will be there before the next section. There is no limit on the number of currency.

The output excel should be as img2 attached - same as img1 except the subtotalled rows and the blank rows between two sections.

The logic I applied as make a cell active and iterate over a loop until the active cell is empty. But in this logic only the three row will be printed in the new excel sheet.
Please suggest how to implement this.

Thanks and Regards,

[Ionelul]

hi,

if you know that always the first cell of a row must have a value in order to copy it, then you can try something like :

do while i < aValue
     if (Worksheet(1).Cells(i,1) <> "" then
        Worksheet(1).Rows(i).Copy
        Worksheets(2).Rows(i).PasteSpecial Paste:=xlPasteValues //here you apply the logic you need and put the row in the other workbook
     end if
     i = i + 1
loop

Ionut

[java_programmer]

Thanks Ionelul for the suggession.
But could u tell me what is aValue? It is no of total rows and how I will get it?

Thanks and regards,

hi,

if you know that always the first cell of a row must have a value in order to copy it, then you can try something like :

do while i < aValue
     if (Worksheet(1).Cells(i,1) <> "" then
        Worksheet(1).Rows(i).Copy
        Worksheets(2).Rows(i).PasteSpecial Paste:=xlPasteValues //here you apply the logic you need and put the row in the other workbook
     end if
     i = i + 1
loop

Ionut

[AndreRet]

aValue will be the number of rows to copy and paste.

Dim iValue As Integer

aValue = Worksheets(1).Rows 'It might be Rowcount in place of Rows, not sure. Play with both options.

This will now copy row by row and paste it row by row.

You can now add code to trap every say 3rd row where you need to add a total sum etc.