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

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

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

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

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

The equivalent VBA line for CTRL+SHIFT+* is:

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

where so i put save as to a file location ?

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

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

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

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

you may as well save the file to a brand new workbook if you want,
Try something like this

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

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

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