teodormircea
03-06-2008, 09:54 AM
Hello every one
I just found a very intersting code , this one , here on this forum, , great job
Dim Myrange As Range
Dim NumKill As Long
Dim KillColumn As String
Dim ActiveColumn As String
Dim AC
'Extract active column as text. Split needs Excel 200
AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
ActiveColumn = AC(0)
KillColumn = InputBox("Enter Column that will be used to map rows for deletion - press Cancel to exit sub", "Row Delete Code", ActiveColumn)
'test that user has not used column IV
If Application.CountA(Range("IV:IV")) > 0 Then
MsgBox "There are no spare columns. Macro will exit", vbCritical
Exit Sub
End If
NumKill = InputBox("Input an Integer less than 65536", "How many rows do you want to kill", Default:=15)
'Run from row 1 of the selected column to the last used cell in that column
Set Myrange = Range(Cells(1, KillColumn), Cells(65536, KillColumn).End(xlUp))
Application.ScreenUpdating = False
If Myrange Is Nothing Then Exit Sub
With Myrange.Offset(0, 1)
.EntireColumn.Insert
.FormulaR1C1 = "=MOD(row(RC[-1])," & NumKill & ")=0"
.AutoFilter Field:=1, Criteria1:="FALSE"
If .Cells.Count > 0 Then .EntireRow.Delete
.EntireColumn.Delete
End With
Cells(1, KillColumn).Activate
Application.ScreenUpdating = True
End Sub
and i wouls like to modify it , in stead of chosing the number of rows to be kiled in the 2nd text box, i would like to make am autofilter , and to have in the second text box , the value that i wouls like to filter, ,count them , display the count in a textbox and have the possibility if i want or not to delete this rows.
If some one has a hint thanks
I just found a very intersting code , this one , here on this forum, , great job
Dim Myrange As Range
Dim NumKill As Long
Dim KillColumn As String
Dim ActiveColumn As String
Dim AC
'Extract active column as text. Split needs Excel 200
AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
ActiveColumn = AC(0)
KillColumn = InputBox("Enter Column that will be used to map rows for deletion - press Cancel to exit sub", "Row Delete Code", ActiveColumn)
'test that user has not used column IV
If Application.CountA(Range("IV:IV")) > 0 Then
MsgBox "There are no spare columns. Macro will exit", vbCritical
Exit Sub
End If
NumKill = InputBox("Input an Integer less than 65536", "How many rows do you want to kill", Default:=15)
'Run from row 1 of the selected column to the last used cell in that column
Set Myrange = Range(Cells(1, KillColumn), Cells(65536, KillColumn).End(xlUp))
Application.ScreenUpdating = False
If Myrange Is Nothing Then Exit Sub
With Myrange.Offset(0, 1)
.EntireColumn.Insert
.FormulaR1C1 = "=MOD(row(RC[-1])," & NumKill & ")=0"
.AutoFilter Field:=1, Criteria1:="FALSE"
If .Cells.Count > 0 Then .EntireRow.Delete
.EntireColumn.Delete
End With
Cells(1, KillColumn).Activate
Application.ScreenUpdating = True
End Sub
and i wouls like to modify it , in stead of chosing the number of rows to be kiled in the 2nd text box, i would like to make am autofilter , and to have in the second text box , the value that i wouls like to filter, ,count them , display the count in a textbox and have the possibility if i want or not to delete this rows.
If some one has a hint thanks