Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2004
    Posts
    113

    Unanswered: macro to save specific start row and column to last record

    hey guys,
    how do i save a spreadsheet from start row = 12 , start col =A until it reaches the last record of the spreadhsheet. I need to know how to do it this way since the spreadsheet will change daily. Currently, im doing it manually by selecting row 12 and using hot key , ctrl+shift+*.

    thanks guys
    Last edited by inho78; 11-17-04 at 14:35.

  2. #2
    Join Date
    Nov 2004
    Posts
    9
    Inho78,
    Let me try to understand...
    What you are trying to do is, go to the Row 12 on the Column A 'till you reach the last record, sekect it and copy to a new Sheet and Save it ?

    Regards

  3. #3
    Join Date
    Nov 2004
    Posts
    9
    Inho78,
    Let me try to understand...
    What you are trying to do is, go to the Row 12 on the Column A 'till you reach the last record, select it and copy to a new Sheet and Save it ?

    Regards

  4. #4
    Join Date
    Sep 2004
    Posts
    113
    Siboneis,
    thanks for replying. The start row is 12 and start column is A but it's not just one column, it goes all the way to column ZE. I have to do this for about 9 diff spreadsheets. They all start on row 12 and column 12 but the whole datasets of each spreadhsheets have different end columns and end rows. The end columns are fixed so I can get the last column but the end row will change daily since new records are updatec each day. Yes you are right but those are the specifics. Im pretty much copying and pasting the whole sheet and pasting it unot another sheet and saving it. Im doing it this way since from row 1 to 11 is the interface where i login and query the spreadheets from a dbms.

    thanks

  5. #5
    Join Date
    Sep 2004
    Posts
    113
    Siboneis,
    thanks for replying. The start row is 12 and start column is A but it's not just one column, it goes all the way to end column. I have to do this for about 9 diff spreadsheets. They all start on row 12 and column 12 but the whole datasets of each spreadhsheets have different end columns and end rows. The end columns are fixed so I can get the last column of each spreadsheet file but the end row will change daily since new records are updated each day. Yes you are right but those are the specifics. Im pretty much copying and pasting the whole sheet and pasting it unto another sheet and saving it. Im doing it this way since from row 1 to 11 is the interface where i login and query the spreadheets from a dbms.

    thanks

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    The equivalent VBA line for CTRL+SHIFT+* is:

    Code:
    Sub test()
        Range("A12").CurrentRegion.Select
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Nov 2004
    Posts
    9
    Try this piece of code, and assign toa button.


    Sub Copy_Range()

    InitialRow = 2 ' The Row where Starts the Data
    LastRow = 1
    LastColumn = 1
    For x = 1 To 60000
    If ActiveSheet.Cells(1, x).Value = Empty Then
    LastColumn = x - 1 ' Here you Found the last column with info
    x = 60000
    End If

    Next x

    For x = InitialRow To 60000
    If ActiveSheet.Cells(x, LastColumn).Value = Empty Then
    LastRow = x
    LastColumn = ActiveSheet.Cells(x - 1, LastRow).Address
    x = 60000
    End If
    Next x
    InitialRow = ActiveSheet.Cells(InitialRow, 1).Address
    Range(InitialRow & ":" & LastColumn).Select



    End Sub

  8. #8
    Join Date
    Sep 2004
    Posts
    113
    where so i put save as to a file location ?

  9. #9
    Join Date
    Sep 2004
    Posts
    113
    InitialRow = 2 ' The Row where Starts the Data
    LastRow = 1
    LastColumn = 1

    so if it's always startrow = 12 start Col = A
    then is the code like this?

    InitialRow = 12 ' The Row where Starts the Data
    LastRow = 12
    LastColumn = A

  10. #10
    Join Date
    Nov 2004
    Posts
    9

    Smile

    Ok, what you'll need to do, open the VisualBasic Editor on your Excel, and on de Module1 ( is by default ), paste this code.

    And after this Save it.

    Go back to the Excel view go to the Tools-Macros choose the macro ( alwasy open the sheet that you want to process ) and Run it.

    on the code that I provide you, the only portion that you need to change is this piece of code :

    InitialRow = 2 ' The Row where Starts the Data

    InitialRow is where you want to start this program to look for data.

    Regards

  11. #11
    Join Date
    Sep 2004
    Posts
    113
    ok,
    i use this to select the range and copy it. It worked! thanks so much but who do i paste the copy to a seperate worksheet and save it as a seperate file?

    Public Sub Copy_Range()

    Range("A12").CurrentRegion.Select
    Selection.Copy
    End Sub

  12. #12
    Join Date
    Nov 2004
    Posts
    9

    Thumbs up

    Ok, you will need to add a new Sheet and use the Selection.Paste.
    If you want to learn how the VB Works, recor macros with the specific things that you want to know and after this edit those ones.

    It so easy to find helpful tricks.

    Regards

  13. #13
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    you may as well save the file to a brand new workbook if you want,
    Try something like this

    Code:
    Sub Test()
        Dim wkbInit As Workbook
        Dim wkbNew As Workbook
        
        Set wkbInit = ActiveWorkbook
        Set wkbNew = Workbooks.Add
        
        'copy the range into the new workbook
        wkbInit.ActiveSheet.Range("A12").CurrentRegion.Copy _
            Destination:=wkbNew.Worksheets(1).Range("A12")
        
        wkbNew.SaveAs "FILEPATH/FILENAME.XLS"
        
        Set wkbInit = Nothing
        Set wkbNew = Nothing
    End Sub
    Just as a sidenote the way to get the lastrow can be done in many ways
    I usually use one of these 2 methods

    1. Cells(rows.count,1).end(xlUp).row

    or

    2. set up a range variable then
    Dim rngfnd as Range
    Set rngfnd = Cells.Find("*", , , , , xlPrevious)
    if Not rngfnd is nothing then

    both of these will find the last row with the second version being more secure as it handles the case when the sheet is empty

    HTH
    Dave

  14. #14
    Join Date
    Sep 2004
    Posts
    113
    this is great advice guys Ill try to run it both ways.
    also,
    1.)how do i create a macro(procedure) to do a character count in a cell so that if it is less than 255 character that it deletes the contents of the cell?

    2.) how do i create a macro(procedure) to delete everything in a certain column and insert a string of characters such as "xxxxx" in each of the cells until it reaches the last record(row) of data

    thanks guys

  15. #15
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    This is pretty straight forward,

    for part 1 loop through your cells you want to check and add the condition

    Something like

    For I = 2 to Lastrow 'Lastrow previously defined
    'replace col with the number of your column that you want to check
    If Len(Cells(I,col).Value) < 255 Then Cells(I,col).Clear
    Next I

    For part 2

    Range(Cells(1stRow,1stCol),Cells(2ndRow,2ndCol)).F ormula = "xxxxxx"

    where the rows and columns are either numbers or variables giving the numbers of your outer most cells

    HTH
    Dave

Posting Permissions

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