VBA loop.

Olympia275
Olympia275 used Ask the Experts™
on
Hello experts

I have this code
Sub loopA()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("sheet2")
For Each cel In Sheets("sheet1").Range("B" & Sheets("sheet1").Range("B1").End(xlDown).Row & ":B" & Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Row)
mySheet.Range("A1") = cel.Value
Next cel
End Sub

Open in new window

which is taking each URL from sheet1 "B1" and placing it into sheet2 "A1" in a loop.  I would like to change the following,

Rather than taking the first one and then next and so on.  Where the used URLs always stay in sheet1, I would like it to remove each URL from sheet1 once it is placed in sheet2, and then the loop should simply always take the first one…

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Delete the row or only the cell?

Author

Commented:
only the cell
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
This seems a little odd to me, but maybe I'm missing something.  Doesn't the loop just keep writing over sheet2:A1 over and over, so it will only have the last value after the loop exists?  Why not just get the last value in the first place?

~bp
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Author

Commented:
That's correct; I will always have in sheet2, the latest value.  However, I need to remove from sheet1.  This code is running for hours and when it is interrupted it always starts Again from the first URL, so I manually have to search and remove the used URLs from my URL list in sheet1

Also, I'm having a memory leak problem and I'm thinking that when VBA needs to memorize all used URLs.  This may use up some memory?  So rather than memorize and go to next, just remove used URLs and always use the first.
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
In that case you can simplify

Put the first cell in B into Sheet 2 and then delete existing data in column B of sheet1

Chris
Sub loopA()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("sheet2")

mySheet.Range("A1") = Sheets("sheet1").Range("B1").End(xlUp).Value
Sheets("sheet1").Range("B:B").Clear

End Sub

Open in new window

Software Quality Lead Engineer
Top Expert 2011
Commented:
Whilst I do not understand the loop parameters ... assuming you have them correct then one way to use your code to the same purpose but as a loop is changing to a row increment and then clearing the content of a given cell.

Note I am assuming in this case that when you post the value to sheet 2 an event is triggered and that therefore you want to use that structure.

Chris
Sub loopA()
Dim cel As Long
Dim mySheet As Worksheet

    Set mySheet = Sheets("sheet2")
    For cel = Sheets("sheet1").Range("B1").End(xlDown).Row To Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Row
        mySheet.Range("A1") = Sheets("sheet1").Range("B" & cel).Value
        Sheets("sheet1").Range("B" & cel).Clear
    Next cel
End Sub

Open in new window

Author

Commented:
you are correct, an event is triggered, which starts on entire program, with many filters...

I have tested your code, it is taking just one URL removing and placing it in sheet2 but is not coming back for the next URL.
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Chris is the expert here, but it sounds like you want something like this:

Sub loopA()
    Dim cel As Range
    Dim mySheet As Worksheet

    Set mySheet = Sheets("sheet2")

    Do While Range("B1").Value <> ""
        mySheet.Range("A1") = Range("B1").Value
        Range("B1").Delete Shift:=xlUp
    Loop

End Sub

Open in new window

~bp

Author

Commented:
Thanks.

It is working now.  Maybe it was a problem with my sheet.  I placed all the codes in a new sheet and it is now working as expected.

Thanks so much.
Carlos RamirezFreelance Web Developer

Commented:
I try to stay away from storing large amounts of data in memory.
I glad you have a solution - I would use something like this:


Sub loopA()

    ' turn screen updates off for speed
    Application.ScreenUpdating = False
   
   
    ' Position where you wish to start in sheet 2
    Sheets(2).Select
    Range("Sheet2!A1").Select
   
    ' Position where you wish to start in sheet 1
    Sheets(1).Select
    Range("B1").Select
   
    ' Loop while there is a value to work with
    Do While ActiveCell.Value <> ""
       
        ' Store value temporarily
        myValue = ActiveCell.Value
       
        ' Remove from Sheet 1
        ActiveCell.ClearContents
       
        ' Jump to next cell down in Sheet 1
        ActiveCell.Offset(1, 0).Select

        ' Jump to sheet 2
        Sheets(2).Select
       
        ' Paste stored value
        ActiveCell.Value = myValue
       
        ' Set hyperlink if desired
        MakeLink ActiveCell, myValue, "", ""
       
        ' Move to next cell in Sheet 2
        ActiveCell.Offset(1, 0).Select
       
        ' jump back to sheet 1
        Sheets(1).Select
               
    Loop

    ' turn screen updates on
    Application.ScreenUpdating = True


End Sub

Sub MakeLink(ByVal cell As Range, ByVal url As String, _
    ByVal txt As String, ByVal tooltip_text As String)
    ' function from: http://vb-helper.com/howto_excel_make_hyperlink.html

    ActiveSheet.Hyperlinks.Add _
        Anchor:=cell, _
        Address:=url, _
        ScreenTip:=tooltip_text, _
        TextToDisplay:=txt
End Sub

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Bill, too kind but just worth a mention ... checking for an empty cell is fine in most cases ... but if there are blanks in a data series then this would stop execution.  The range as used by the OP has the benefit of addressing non contiguous data ranges.

Chris
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Good point, thanks Chris, so we would likely have to determine the full "height" of the range up front and then loop that many times in my approach?

I interpreted the OP desire slightly differently, and thought they wanted to actually remove the cells once processed rather than just clear them, which doesn't seem to have been the desire.

~bp

Author

Commented:
thanks for all of you. Chris and Bill, I think that was the problem.  Why bills code wasn't executing. and for slycoder your code Is not overwriting in sheet2 it is simply copying and adding rows...
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Allowing for no formatting on a cell the clear is pretty much the same as delete.  The beauty of the range using the built in methods is it is precise so for example:

Sheets("sheet1").Range("B1").End(xlDown) is the last populated cell in the first block of data
Sheets("sheet1").Range("B" & rows.count).End(xlUp) is the last populated cell in the sheet

Hence no end to numbers of blank cells within the range of first to last.  The range can of course be processed to delete and move the cells up as long as the deleted cell is the first one in the range.

For info another ranging approach that allows for rows > 1 as the first populated cell and random other row as the last is to use the 'usedrange':

activesheet.usedrange.cells(1).row ... row number of the first row with data entered
?activesheet.usedrange.rows(activesheet.usedrange.rows.count).row ... last row with data

In this case the row data returned would include any cells populated and subsequently deleted so is sometimes better and sometimes worse than a simple range.

Chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial