とりあえず 最終列、行を選択するマクロを作ってみました。
Sub allctlend() For i = 1 To Sheets.Count Sheets(i).Select With ActiveSheet.UsedRange MaxRow = .Rows(.Rows.Count).Row MaxCol = .Columns(.Columns.Count).Column End With Sheets(i).Cells(MaxRow, MaxCol).Select Next End Sub
Sub allctlend() Dim MaxRow As Long Dim MaxCol As Long Dim i As Long Dim b As Long For i = 1 To Sheets.Count Sheets(i).Select With ActiveSheet.UsedRange MaxRow = .Rows(.Rows.Count).Row MaxCol = .Columns(.Columns.Count).Column End With For b = MaxRow To 1 Step -1 If Application.WorksheetFunction.CountA(ActiveSheet.Range(CStr(b) & ":" & CStr(b))) = 0 Then Sheets(i).Range(CStr(b) & ":" & CStr(b)).Delete Shift:=xlUp End If Next b With ActiveSheet.UsedRange MaxRow = .Rows(.Rows.Count).Row MaxCol = .Columns(.Columns.Count).Column End With Sheets(i).Cells(MaxRow, MaxCol).Select Next End Sub
こちらは どうかな?
▽3
●
きゃづみぃ ●40ポイント ベストアンサー |
もひとつ
Sub allctlend2() Dim MaxRow As Long Dim MaxCol As Long Dim i As Long Dim b As Long For i = 1 To Sheets.Count Sheets(i).Select With ActiveSheet.UsedRange MaxRow = .Rows(.Rows.Count).Row MaxCol = .Columns(.Columns.Count).Column End With For b = MaxRow To 1 Step -1 If Application.WorksheetFunction.CountA(ActiveSheet.Range(CStr(b) & ":" & CStr(b))) = 0 Then Sheets(i).Range(CStr(b) & ":" & CStr(b)).Delete Shift:=xlUp Else Exit For End If Next b With ActiveSheet.UsedRange MaxRow = .Rows(.Rows.Count).Row MaxCol = .Columns(.Columns.Count).Column End With Sheets(i).Cells(MaxRow, MaxCol).Select Next End Sub
最終行と判断された行からデータがある行までを削除。