Replies

Hello together,

you can adjust the UsedRange for the active sheet by this code:

Sub adapt_UsedRange_in_active_sheet()
ActiveSheet.UsedRange ‘readjustment of UsedRange
End Sub

For all sheets in a workbook you can use this code:

Sub adapt_UsedRange_in_all_sheets_of_a_workbook()
‘only working in a loop, because sheet must be active
‘by Uwe Küstner – Germany
Dim Ws As Worksheet
Dim oWs As Object
Set oWs = ActiveSheet
Application.ScreenUpdating = False
For Each Ws In Worksheets
Ws.Activate
ActiveSheet.UsedRange ‘readjustment of UsedRange
Next Ws
Application.ScreenUpdating = True
oWs.Activate
End Sub

Hello,

lines are numbered from A1 to last used row in the sheet:

Sub TestNo4()
Range(Cells(1, “A”), Cells(ActiveSheet.Cells(Cells(Rows.Count, “A”).End(xlUp).Row, 1).Row, “A”)).Select
Dim x As Long, y As Integer
x = ActiveSheet.UsedRange.Rows.Count
y = ActiveCell.Column
Range(“A1”) = 1
Range(“A1”).AutoFill Range(Cells(ActiveCell.Row, y), Cells(x, y)), xlFillSeries
Range(“A1”).Select
End Sub

Regards,
Beate