Posts by FreddyKrueger

Re: Avoiding message that pops up during execution

Thanx alot aadarsh

It's true what you say WillR.
My code is really sloppy. Should be edited. But it is the second procedure i've ever written so hopefully i'm more efficient next time

While I run my code the following message keeps popping up when I try to copy and paste a range.
"Do you want to raplace contens of destination cells"
How can this be avoided

Here is part of my code:

For m = 2 To LastRow
    
    Sheets("UniverseDataFOR").Activate
    If Cells(m, 2) = Fund Then
    Range(Cells(m, 1), Cells(m + 24, 26)).Select
    Selection.Copy
    Sheets("Info").Select
    Rows(n + 2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    n = n + 1

Edit: WillR. Freddy - Please use the code tags so that your code is easier to read. I have amended the code above. Thanks.

Re: Paste Cells based on number of consecutive similar values

The problem is, your code still takes only on row at a times and moves it to another worksheet. I want a code which will move al the consecutive rows, where the string "fund" matched the value in the second column, at once

I hope this will make sense
I have a huge spreadsheet.
The second column can contain one of say 10 diffirent funds (eg: "EB IF" or "EB NB" or "DLIF"). My first move is to sort this spreadsheet based on the column.

My previous procedure used a for loop that looked at all the values in the second column (from row 1 to the last row that contains data) one by one and if they matched a certain input (eg "EB IF") the whole row were pasted to another sheet. :
This is part of what I had

Dim m As Integer
    Dim n As Integer
    n = 1
   
    Dim LastRow As Integer
    Sheets("UniverseDataMatrix").Activate
    LastRow = Range("A65536").End(xlUp).Row
     
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
            
    For m = 2 To LastRow
    Sheets("UniverseDataMatrix2").Activate
    
    If Cells(m, 3) = Fund Then
    Rows(m).Select
    Selection.Copy
    Sheets("Info").Select
    Rows(2 + n).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks :=False, Transpose:=False
    n = n + 1
    Next m

This takes way too long too execute.
I want to paste the whole range at once (Eg: All the "EB IF" consecutive values). Thus I want the "m" forloop to find the first valued, count the number of consecutive values and based on that copy and paste the required range.

Please Help

Edit: WillR: Freddy, Please use the code tags when posting code. I have edited the above (2nd post of yours today). Reasons...
1. It saves me a job
2. It makes reading the code easier.
Please can you take the trouble to learn how to use the tags & use them in future. There is a TEST AREA for this purpose. Thankyou.

Re: Doiing a loop based on the number of non-empty rows

Thanx Matt

This works even better

F

Re: Doiing a loop based on the number of non-empty rows

Thanx alot.

I would like to do a loop equal to the number of rows that is not empty in my workbook.

What the code does is it looks for a value in the second column of the workbook and if it exits, it copies the whole row and pastes it onto another workbook, row by row.

My problem is that my original workbook may be anything from 25 to 2000 rows long but I do not want my loop to execute 2000 times each time it runs.
How can I count the number of nonempty rows and only let my loop execute that number of times.

This is my first post. Any help would be hugely appreciated

F