+ Reply to Thread
Results 1 to 4 of 4

Loop on the Row as integer, getting integer into a range

Hybrid View

  1. #1
    Registered User
    Join Date
    05-07-2013
    Location
    Amsterdam
    MS-Off Ver
    Excel 2013
    Posts
    72

    Loop on the Row as integer, getting integer into a range

    Hi,
    i am creating a loop based on Row in column A. I am trying to do this with integers.

    Basically i have in Column A1 to A500 all kind of users.
    A1 Stephan
    A2 Bob
    A3 Mose
    etc

    I want to copy from the same row AG:AO to other sheet or do some action.
    I dont know how to bind this Integer into the Range or string.

    Dim iRow As Integer
    Dim iCol As Integer
    Dim iEndCol As Integer
    Dim wb As ThisWorkbook
    Dim ws As Worksheet
    
    iRow = 2
    iCol = 33 ' AG
    iEndCol = 41 'AO
    
    Do While Cells(iRow, 1) <> ""
    
    ' now here i want the iRow into the string
         'Range("AG2:AO2").Copy But the 2 should be Irow in the string
         'Sheets("Data Fetched").Range("AG: & iRow" & "AO: & iRow").Copy
         'perhaps;     
         Sheets("Data Fetched").Range(Cells(iRow, iCol), Cells(iRow, iEndCol)).Copy
         
        
    
      
     iRow = iRow + 1
        
     Loop
    Last edited by Fean; 03-20-2018 at 07:07 AM.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Loop on the Row as integer, getting integer into a range

    Perhaps:

    Range("AG" & Irow & ":AO" & Irow).Copy
    You almost had it, but put ":" after AG and AO.

    However, it may be more efficient to just find the last row in column A, and then copy all the data in one shot:

    Sub Fean()
    
    Dim iRow As Integer
    Dim lRow As Integer
    Dim iCol As Integer
    Dim iEndCol As Integer
    Dim wb As Workbook
    Dim ws As Worksheet
    
    iRow = 2
    iCol = 33 ' AG
    iEndCol = 41 'AO
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data Fetched")
    lRow = ws.Range("A" & Rows.Count).End(xlUp).Row 'last row with data in column A
    'copy the whole range in one shot:
    ws.Range(ws.Cells(iRow, iCol), ws.Cells(lRow, iEndCol)).Copy
    
    End Sub
    Last edited by Arkadi; 03-20-2018 at 07:14 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    05-07-2013
    Location
    Amsterdam
    MS-Off Ver
    Excel 2013
    Posts
    72

    Re: Loop on the Row as integer, getting integer into a range

    Quote Originally Posted by Arkadi View Post
    Perhaps:

    Range("AG" & Irow & ":AO" & Irow).Copy
    You almost had it, but put ":" after AG and AO.
    Aahhhh of course! That makes sense, breaking my head my here!

    Thanks!

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Loop on the Row as integer, getting integer into a range

    Just edited my post by adding a more efficient solution... one row at a time will be much slower than a single copy for the whole range...

    Either way, glad to help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 07-25-2014, 01:28 AM
  2. Replies: 2
    Last Post: 06-30-2013, 03:30 PM
  3. Create a loop by defining X as integer, to shorten length of code
    By ahmerjaved in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-11-2013, 01:11 PM
  4. Dim integer in For loop does not work, why?
    By 123wc in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-30-2011, 12:48 PM
  5. Replies: 3
    Last Post: 10-27-2011, 05:20 PM
  6. [SOLVED] Logical test for integer or non- integer
    By MHRW in forum Excel General
    Replies: 7
    Last Post: 08-04-2011, 12:46 AM
  7. Count the number of occurrences of an integer withing a larger integer
    By nnktran in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2010, 01:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1