回答編集履歴
2
コード修正
answer
CHANGED
|
@@ -11,15 +11,15 @@
|
|
|
11
11
|
Set rng = Range("A1").CurrentRegion
|
|
12
12
|
Set rng = Intersect(rng, rng.Offset(1, 1))
|
|
13
13
|
|
|
14
|
-
Dim
|
|
14
|
+
Dim i As Long
|
|
15
|
-
For
|
|
15
|
+
For i = rng.Rows.Count To 1 Step -1
|
|
16
|
-
If WorksheetFunction.Count(
|
|
16
|
+
If WorksheetFunction.Count(rng.Rows(i)) = 0 Then
|
|
17
|
-
|
|
17
|
+
rng.Rows(i).EntireRow.Delete
|
|
18
18
|
End If
|
|
19
19
|
Next
|
|
20
|
-
For
|
|
20
|
+
For i = rng.Columns.Count To 1 Step -1
|
|
21
|
-
If WorksheetFunction.Count(
|
|
21
|
+
If WorksheetFunction.Count(rng.Columns(i)) = 0 Then
|
|
22
|
-
|
|
22
|
+
rng.Columns(i).EntireColumn.Delete
|
|
23
23
|
End If
|
|
24
24
|
Next
|
|
25
25
|
End Sub
|
|
@@ -33,21 +33,22 @@
|
|
|
33
33
|
**最終行、最終列にデータがある場合のコードは下記で。**
|
|
34
34
|
|
|
35
35
|
```vba
|
|
36
|
-
Public Sub
|
|
36
|
+
Public Sub DelBlankRowCol()
|
|
37
37
|
|
|
38
38
|
Dim rng As Range
|
|
39
|
-
|
|
39
|
+
With Range("A1").CurrentRegion
|
|
40
|
-
|
|
40
|
+
Set rng = .Offset(1, 1).Resize(.Rows.Count - 2, .Columns.Count - 2)
|
|
41
|
+
End With
|
|
41
42
|
|
|
42
|
-
Dim
|
|
43
|
+
Dim i As Long
|
|
43
|
-
For
|
|
44
|
+
For i = rng.Rows.Count To 1 Step -1
|
|
44
|
-
If WorksheetFunction.Count(
|
|
45
|
+
If WorksheetFunction.Count(rng.Rows(i)) = 0 Then
|
|
45
|
-
|
|
46
|
+
rng.Rows(i).EntireRow.Delete
|
|
46
47
|
End If
|
|
47
48
|
Next
|
|
48
|
-
For
|
|
49
|
+
For i = rng.Columns.Count To 1 Step -1
|
|
49
|
-
If WorksheetFunction.Count(
|
|
50
|
+
If WorksheetFunction.Count(rng.Columns(i)) = 0 Then
|
|
50
|
-
|
|
51
|
+
rng.Columns(i).EntireColumn.Delete
|
|
51
52
|
End If
|
|
52
53
|
Next
|
|
53
54
|
End Sub
|
1
コード追記
answer
CHANGED
|
@@ -25,4 +25,30 @@
|
|
|
25
25
|
End Sub
|
|
26
26
|
```
|
|
27
27
|
|
|
28
|
-
データが数値だけなの場合はこれでいいですが、文字もあるなら、CountA関数に変更してください。
|
|
28
|
+
データが数値だけなの場合はこれでいいですが、文字もあるなら、CountA関数に変更してください。
|
|
29
|
+
|
|
30
|
+
追記
|
|
31
|
+
---
|
|
32
|
+
|
|
33
|
+
**最終行、最終列にデータがある場合のコードは下記で。**
|
|
34
|
+
|
|
35
|
+
```vba
|
|
36
|
+
Public Sub DelBlankRowCol2()
|
|
37
|
+
|
|
38
|
+
Dim rng As Range
|
|
39
|
+
Set rng = Range("A1").CurrentRegion
|
|
40
|
+
Set rng = rng.Offset(1, 1).Resize(rng.Rows.Count - 2, rng.Columns.Count - 2)
|
|
41
|
+
|
|
42
|
+
Dim r As Range
|
|
43
|
+
For Each r In rng.Rows
|
|
44
|
+
If WorksheetFunction.Count(r) = 0 Then
|
|
45
|
+
r.EntireRow.Delete
|
|
46
|
+
End If
|
|
47
|
+
Next
|
|
48
|
+
For Each r In rng.Columns
|
|
49
|
+
If WorksheetFunction.Count(r) = 0 Then
|
|
50
|
+
r.EntireColumn.Delete
|
|
51
|
+
End If
|
|
52
|
+
Next
|
|
53
|
+
End Sub
|
|
54
|
+
```
|