+ Reply to Thread
Results 1 to 12 of 12

Select cells on both sides of active cell, then select all rows down table with data

  1. #1
    Registered User
    Join Date
    04-15-2021
    Location
    Moose Jaw, SK
    MS-Off Ver
    Office 365
    Posts
    5

    Select cells on both sides of active cell, then select all rows down table with data

    I'm a VBA beginner.

    I need to copy information in a current Excel file to another file and save as csv. I have 95% of the code working as I want, but one part I can't solve. I've spent the better part of two days experimenting with possible solutions to no avail.

    The original Excel files have been created more like a 'Word Document' than an Excel spreadsheet which has created a several challenges:
    1. The 'starting row' for the 'table' I need to select and copy is not the same file to file, e.g. the first row of data is on Row 8, or Row 11, or 9, etc.
    2. Column 1 and Column 2 of the table can be blank (as this information is optional), so I am using Column 3 which is 'required info' to find the actual 'starting row' for the table and set an "ActiveCell"
    3. The number of rows in the table varies from form to form (one could have 49, another 80, another 200, etc.)

    This code gets me the three 'pieces' I need:

    'Selects the correct cell to find the 'starting row' for the table on every file
    Worksheets(1).Select
    Worksheets(1).Range("C4").Select
    Cells.Find(What:="QTR", After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
    If IsEmpty(ActiveCell.Value) = True Then
    ActiveCell.Offset(, 1).Range("A1").Select
    End If

    ' Finds the last row of data in the table after finding the proper 'ActiveCell'
    last_QTR_Row = Cells(Rows.Count, 3).End(xlUp).Row

    ' Selects the required range for the table around the ActiveCell
    Range(Cells(Selection.Row, 1), Cells(Selection.Row, 10)).Select

    What I can't find the answer for (and have tried for hours with trial and error) is how can I get the 2nd and 3rd elements to work together (or totally take a different approach that works better) to: 1) Select the needed range around the ActiveCell (Column 1: Column 10) and, 2) Select down to the 'last row' of the table as determined by the final cell with data in Column 3.

    Any help would be greatly appreciated. I have included a sample file in this post. Thank you so much in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,594

    Arrow Re: Select cells on both sides of active cell, then select all rows down table with data


    You lost me as a good VBA procedure most of the time must not select any cell !

    It's easier to just elaborate what should be done according to rows, columns or cells addresses
    and to attach the expected text file

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,594

    Cool Try this


    According to your attachment a VBA demonstration as a beginner starter to export rows to a text file w/o another worksheet
    to paste to the Sheet1 (Page 1) worksheet module :

    PHP Code: 
    Sub Demo1()
            
    F% = FreeFile
            Open ThisWorkbook
    .Path "\Export .csv" For Output As #F
        
    With UsedRange.Rows
            
    For R& = Application.Match("FIELD NAMES", .Columns(1), 0) + 2 To Cells(Rows.Count3).End(xlUp).Row
                
    Print #F, Join(Application.Index(.Item(R).Value, 1, 0), ",")
            
    Next
        End With
            Close 
    #F
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !

  4. #4
    Registered User
    Join Date
    04-15-2021
    Location
    Moose Jaw, SK
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Try this

    Thank you, Marc L. I so appreciate your help.

    I knew there was going to be a much easier way to do what I was trying to do.
    Unfortunately, when I step through your procedure I get an "Object Required" error at the 'With.UsedRange.Rows' line.
    I've tried a couple times to resolve the error with no luck.

    I apologize for not including more info in the spreadsheet from the first post. It was my first time posting here.
    I've included the spreadsheet with what the 'finished export data' (ExportData tab) will need to look like in the new file.

    I've hacked together some code to do everything except find the 'Field Name table' information on each file and get it pasted, starting at 'F2' on the new sheet.
    I'm unsure how to do this because, as I mentioned before, the information can be different lengths on each file and start on different rows.
    Also the 'Field Name' and 'RM' columns can be empty or have incomplete data. The 'QTR' column data is required for each form.

    Once it is there though, the rest of the VBA works to pull in the other required data.
    In the end, my VBA will loop through a folder with 200+ files and read the data on Sheet1 of each file, save 'exported data' to a new file, close, and move to the next file.

    I hope the new spreadsheet will help.

    Thank you again for your help it will clearly move me lightyears ahead in understanding best practices for this.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,594

    Question Re: Select cells on both sides of active cell, then select all rows down table with data


    Where did you paste the code ? Not as per the direction of my previous post

  6. #6
    Registered User
    Join Date
    04-15-2021
    Location
    Moose Jaw, SK
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Select cells on both sides of active cell, then select all rows down table with data

    Hi Marc L. I did paste it where you instructed me, at least I think I did. I have included a screenshot of the Project Window.

    I really do appreciate your help and patience.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by KCourt; 04-16-2021 at 12:57 AM. Reason: Added the file with the macro

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,594

    Exclamation Re: Select cells on both sides of active cell, then select all rows down table with data


    Wrong location, same player shoot again ! See post #3

    If you want a standard module rather than the explicit place explained in post #3
    you must add a valid worksheet reference - example : ActiveSheet - to the UsedRange statement like explained in VBA help

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,594

    Cool Or


    According to the last attachment and to your initial indirect logic this is the part to copy data :

    PHP Code: 
        With Sheet1
            
    .Range(.Cells(Application.Match("FIELD NAMES", .UsedRange.Columns(1), 0) + 21), .Cells(.Rows.Count10).End(xlUp)).Copy Sheet5.[E2]
        
    End With 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 04-16-2021 at 11:58 AM.

  9. #9
    Registered User
    Join Date
    04-15-2021
    Location
    Moose Jaw, SK
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Select cells on both sides of active cell, then select all rows down table with data

    Thank you Marc L.

    I learned how to paste the macro into the proper place. You can't step through it to 'test it', but it runs beautifully as expected. Amazing!

    Your more expert code is a bit beyond my complete comprehension, but I think I get the general flow.

    I'm going to work with your 2nd code piece to take my next step for the 'full transformation' of the data I shared with the 2nd spreadsheet.

    Thank you for your continued help.

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,594

    Cool Try this !


    Thanks for the rep' !

    According to your last attachment the direct CSV export rather than using another worksheet
    to paste to a standard module but only working with the active sheet :

    PHP Code: 
    Sub Demo2()
      Const 
    ","
            
    Application.Match("FIELD NAMES"ActiveSheet.UsedRange.Columns(1), 0)
        If 
    IsNumeric(VThen
            S
    $ = vbNewLine & [B3].Text & [B4].Text & [I3].Text & [I4].Text D
            F
    % = FreeFile
            Open ThisWorkbook
    .Path "\Export .csv" For Output As #F
            
    Print #F, "Contact,Farm,Phone,Date,FIELD NAMES,RM,QTR,SEC,TWP,RNG,MER,Acres,2018 CROP,Seeded Date";
        
    With Cells(V1).CurrentRegion.Rows
            
    For R& = 3 To .Count 1:  Print #F, S; Join(Application.Index(.Item(R).Value, 1, 0), D);:  Next
        
    End With
            Close 
    #F
        
    End If
    End Sub 

  11. #11
    Registered User
    Join Date
    04-15-2021
    Location
    Moose Jaw, SK
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Or

    Wow, Marc L.!! I finally had the chance to work this bit of code into the larger piece I have been working on and it works incredibly! Thank you, so much.

    You're 'Demo2' sub procedure looks amazing too. It feels a bit beyond my ability to tailor it to the specific needs for this project.

    Example: as the code loops through each file in the folder and saves the required information, I need each new file to save with the cell value of B2 (farm name) and the 'year' part of the value contained in M2 of the new table, separated with a dash: Green Acres Farms - 2018.csv, for example (for all 250 files).

    I'm going to try to find a way to append the code that currently does that file naming for me to your Demo2, but if I 'blow up' what you've made, I will go back to use my very ugly, piecemeal approach for the remainder of this project. I am still WAY ahead because of your help with the 'mass selection method' for the tables that vary from file to file on Sheet 1! So amazing.

    Your code is 100x times more elegant than my piecemeal approach. You have given me a whole new understanding of the power of VBA and what someone who is well-versed in it is capable of. Is there a specific resource you started with to learn the foundations of VBA? Thank you, again.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,594

    Arrow Re: Select cells on both sides of active cell, then select all rows down table with data


    In fact VBA is a mix of Visual Basic and an Excel objects model with Excel features as well

    A good way to start learning is to activate the Macro Recorder and operate manually in order to see which statements are used.
    But as a generated code must be rewritten to be clean, comparing with forums - or just asking on - can lead to some good practice

+ 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. [SOLVED] Select entire rows of range of active cells
    By go3go3go in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-26-2021, 01:20 PM
  2. [SOLVED] how to select cells above active cell and do a sum product with other row in VBA
    By Raja10 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2020, 10:03 PM
  3. Using offset function to select a range of cells based on active cell location
    By laxminarayana in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-25-2017, 07:49 AM
  4. [SOLVED] Select filtered data below ACTIVE CELL, without using LOOPs
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2015, 10:25 PM
  5. [SOLVED] Using code to select cells that are two cells to the left of the currently active cell
    By LaffyAffy13 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-25-2013, 01:56 PM
  6. Replies: 1
    Last Post: 07-08-2012, 08:47 AM
  7. macro to find data and select from active cell to the last row
    By Lost in Alabama in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2006, 04:10 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