Autofilling
 

Hi,

I have arrays in 45 sheets and 11 workbooks. They have different amount of
rows (usually they do not go more than 200 rows). I want to do the autofill
from 1 to N, because after I have applied my delete macro and missing some
rows, so I need to re-sort by C column.

Thanks

Autofilling
 

Faboboren,

Lots of variables in that question that you need to first answer.

Where are the workbooks? Currently open, or in a folder? Do you have a blank column for the
autofill, or do you need to insert one, and which column do you want to use? Do you want to apply it
to all sheets, or just with some defined array? Is the array the entire sheet, or part of the
sheet? Is the array sized differently than adjacent blaocks of data?

HTH,
Bernie
MS Excel MVP


"Faboboren" wrote in message
...
Hi,

I have arrays in 45 sheets and 11 workbooks. They have different amount of
rows (usually they do not go more than 200 rows). I want to do the autofill
from 1 to N, because after I have applied my delete macro and missing some
rows, so I need to re-sort by C column.

Thanks

Autofilling
 

Hi Bernie,

Thanks for your answer.

Files are opened
I want to use column C
I have in column C after my macro deleted some rows:
1
2
3
6
7
8
10
13
14
As you can see I am missing: 4, 5,9,11,12.
In this example I need to ascending re-sort from 1 to 9 (I do not have a
blank column nor I want to insert a new column
I want to apply to all sheets in column C and workbooks(42 sheets and 11
workbooks). Arrays are variable, could go from 10 rows to 200 rows (aprox).
Columns from A to M.

"Bernie Deitrick" wrote:

Faboboren,

Lots of variables in that question that you need to first answer.

Where are the workbooks? Currently open, or in a folder? Do you have a blank column for the
autofill, or do you need to insert one, and which column do you want to use? Do you want to apply it
to all sheets, or just with some defined array? Is the array the entire sheet, or part of the
sheet? Is the array sized differently than adjacent blaocks of data?

HTH,
Bernie
MS Excel MVP


"Faboboren" wrote in message
...
Hi,

I have arrays in 45 sheets and 11 workbooks. They have different amount of
rows (usually they do not go more than 200 rows). I want to do the autofill
from 1 to N, because after I have applied my delete macro and missing some
rows, so I need to re-sort by C column.

Thanks

Autofilling
 

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub ReNumColC()
Dim myB As Workbook
Dim myS As Worksheet
Dim myRow As Long

For Each myB In Application.Workbooks
If myB.Windows(1).Visible Then
For Each myS In myB.Worksheets
myRow = myS.Cells(Rows.Count, 3).End(xlUp).Row
With myS.Range("C1:C" & myRow)
.Formula = "=ROW()"
.Value = .Value
End With
Next myS
End If
Next myB
End Sub


"Faboboren" wrote in message
...
Hi Bernie,

Thanks for your answer.

Files are opened
I want to use column C
I have in column C after my macro deleted some rows:
1
2
3
6
7
8
10
13
14
As you can see I am missing: 4, 5,9,11,12.
In this example I need to ascending re-sort from 1 to 9 (I do not have a
blank column nor I want to insert a new column
I want to apply to all sheets in column C and workbooks(42 sheets and 11
workbooks). Arrays are variable, could go from 10 rows to 200 rows (aprox).
Columns from A to M.

"Bernie Deitrick" wrote:

Faboboren,

Lots of variables in that question that you need to first answer.

Where are the workbooks? Currently open, or in a folder? Do you have a blank column for the
autofill, or do you need to insert one, and which column do you want to use? Do you want to apply
it
to all sheets, or just with some defined array? Is the array the entire sheet, or part of the
sheet? Is the array sized differently than adjacent blaocks of data?

HTH,
Bernie
MS Excel MVP


"Faboboren" wrote in message
...
Hi,

I have arrays in 45 sheets and 11 workbooks. They have different amount of
rows (usually they do not go more than 200 rows). I want to do the autofill
from 1 to N, because after I have applied my delete macro and missing some
rows, so I need to re-sort by C column.

Thanks

Autofilling
 

Hi Bernie,

It 's working. But because my first row to re-sort is at C7 and header at
C6, it is doing the autofill from 1 to N from C1, and I need from C7 #1.

Thanks


"Bernie Deitrick" wrote:

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub ReNumColC()
Dim myB As Workbook
Dim myS As Worksheet
Dim myRow As Long

For Each myB In Application.Workbooks
If myB.Windows(1).Visible Then
For Each myS In myB.Worksheets
myRow = myS.Cells(Rows.Count, 3).End(xlUp).Row
With myS.Range("C1:C" & myRow)
.Formula = "=ROW()"
.Value = .Value
End With
Next myS
End If
Next myB
End Sub


"Faboboren" wrote in message
...
Hi Bernie,

Thanks for your answer.

Files are opened
I want to use column C
I have in column C after my macro deleted some rows:
1
2
3
6
7
8
10
13
14
As you can see I am missing: 4, 5,9,11,12.
In this example I need to ascending re-sort from 1 to 9 (I do not have a
blank column nor I want to insert a new column
I want to apply to all sheets in column C and workbooks(42 sheets and 11
workbooks). Arrays are variable, could go from 10 rows to 200 rows (aprox).
Columns from A to M.

"Bernie Deitrick" wrote:

Faboboren,

Lots of variables in that question that you need to first answer.

Where are the workbooks? Currently open, or in a folder? Do you have a blank column for the
autofill, or do you need to insert one, and which column do you want to use? Do you want to apply
it
to all sheets, or just with some defined array? Is the array the entire sheet, or part of the
sheet? Is the array sized differently than adjacent blaocks of data?

HTH,
Bernie
MS Excel MVP


"Faboboren" wrote in message
...
Hi,

I have arrays in 45 sheets and 11 workbooks. They have different amount of
rows (usually they do not go more than 200 rows). I want to do the autofill
from 1 to N, because after I have applied my delete macro and missing some
rows, so I need to re-sort by C column.

Thanks

Autofilling
 

Change this:

With myS.Range("C1:C" & myRow)
.Formula = "=ROW()"

to this:

With myS.Range("C7:C" & myRow)
.Formula = "=ROW()-6"



HTH,
Bernie
MS Excel MVP


"Faboboren" wrote in message
...
Hi Bernie,

It 's working. But because my first row to re-sort is at C7 and header at
C6, it is doing the autofill from 1 to N from C1, and I need from C7 #1.

Thanks


"Bernie Deitrick" wrote:

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub ReNumColC()
Dim myB As Workbook
Dim myS As Worksheet
Dim myRow As Long

For Each myB In Application.Workbooks
If myB.Windows(1).Visible Then
For Each myS In myB.Worksheets
myRow = myS.Cells(Rows.Count, 3).End(xlUp).Row
With myS.Range("C1:C" & myRow)
.Formula = "=ROW()"
.Value = .Value
End With
Next myS
End If
Next myB
End Sub


"Faboboren" wrote in message
...
Hi Bernie,

Thanks for your answer.

Files are opened
I want to use column C
I have in column C after my macro deleted some rows:
1
2
3
6
7
8
10
13
14
As you can see I am missing: 4, 5,9,11,12.
In this example I need to ascending re-sort from 1 to 9 (I do not have a
blank column nor I want to insert a new column
I want to apply to all sheets in column C and workbooks(42 sheets and 11
workbooks). Arrays are variable, could go from 10 rows to 200 rows (aprox).
Columns from A to M.

"Bernie Deitrick" wrote:

Faboboren,

Lots of variables in that question that you need to first answer.

Where are the workbooks? Currently open, or in a folder? Do you have a blank column for the
autofill, or do you need to insert one, and which column do you want to use? Do you want to
apply
it
to all sheets, or just with some defined array? Is the array the entire sheet, or part of the
sheet? Is the array sized differently than adjacent blaocks of data?

HTH,
Bernie
MS Excel MVP


"Faboboren" wrote in message
...
Hi,

I have arrays in 45 sheets and 11 workbooks. They have different amount of
rows (usually they do not go more than 200 rows). I want to do the autofill
from 1 to N, because after I have applied my delete macro and missing some
rows, so I need to re-sort by C column.

Thanks

Autofilling
 

Bernie,

It is working beautiful!!

Thank you very much

"Bernie Deitrick" wrote:

Change this:

With myS.Range("C1:C" & myRow)
.Formula = "=ROW()"

to this:

With myS.Range("C7:C" & myRow)
.Formula = "=ROW()-6"



HTH,
Bernie
MS Excel MVP


"Faboboren" wrote in message
...
Hi Bernie,

It 's working. But because my first row to re-sort is at C7 and header at
C6, it is doing the autofill from 1 to N from C1, and I need from C7 #1.

Thanks


"Bernie Deitrick" wrote:

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub ReNumColC()
Dim myB As Workbook
Dim myS As Worksheet
Dim myRow As Long

For Each myB In Application.Workbooks
If myB.Windows(1).Visible Then
For Each myS In myB.Worksheets
myRow = myS.Cells(Rows.Count, 3).End(xlUp).Row
With myS.Range("C1:C" & myRow)
.Formula = "=ROW()"
.Value = .Value
End With
Next myS
End If
Next myB
End Sub


"Faboboren" wrote in message
...
Hi Bernie,

Thanks for your answer.

Files are opened
I want to use column C
I have in column C after my macro deleted some rows:
1
2
3
6
7
8
10
13
14
As you can see I am missing: 4, 5,9,11,12.
In this example I need to ascending re-sort from 1 to 9 (I do not have a
blank column nor I want to insert a new column
I want to apply to all sheets in column C and workbooks(42 sheets and 11
workbooks). Arrays are variable, could go from 10 rows to 200 rows (aprox).
Columns from A to M.

"Bernie Deitrick" wrote:

Faboboren,

Lots of variables in that question that you need to first answer.

Where are the workbooks? Currently open, or in a folder? Do you have a blank column for the
autofill, or do you need to insert one, and which column do you want to use? Do you want to
apply
it
to all sheets, or just with some defined array? Is the array the entire sheet, or part of the
sheet? Is the array sized differently than adjacent blaocks of data?

HTH,
Bernie
MS Excel MVP


"Faboboren" wrote in message
...
Hi,

I have arrays in 45 sheets and 11 workbooks. They have different amount of
rows (usually they do not go more than 200 rows). I want to do the autofill
from 1 to N, because after I have applied my delete macro and missing some
rows, so I need to re-sort by C column.

Thanks

Autofilling
 

It is working beautiful!!

Thank you very much

You're welcome!

Bernie