PDA

View Full Version : VB code for delete the columns



night_p
11-17-2011, 04:47 AM
Hi

I have the file that contaions 1900 column.

the number "120" is repeated frequently.

How can I delete the columns containing "120"?

( in other hand i want to del the ogg columns)

in the below pic I show my meaning.

here I del the column manually ( its to hard to del 1900 columns one by one )

mancubus
11-17-2011, 05:28 AM
hi and wellcome to VBAX.

do you really have 1900 columns of data?

or do you actually mean "clearing '120's from CELLS"?

or do you actually mean "deleting rows in which any cell contains the value 120"?

mancubus
11-17-2011, 06:08 AM
never mind.

works on ActiveSheet. Change "ActiveSheet.UsedRange" to suit.


Sub Del_Col_Cond()
Dim i As Integer
For i = ActiveSheet.UsedRange.Columns.Count To 1 Step -1
If Application.CountIf(Columns(i), 120) > 0 Then
Columns(i).Delete
End If
Next i
End Sub



Sub Del_Row_Cond()
Dim i As Integer
For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If Application.CountIf(Rows(i), 120) > 0 Then
Rows(i).Delete
End If
Next i
End Sub



Sub Clear_Cell_Cond()
Dim cll As Range
For Each cll In ActiveSheet.UsedRange
If cll.Value = 120 Then cll.ClearContents
Next
End Sub

night_p
11-17-2011, 08:32 AM
hi and wellcome to VBAX.

do you really have 1900 columns of data?



Tanx alot Mr.mancubus

yes,I have it.



or do you actually mean "deleting rows in which any cell contains the value 120"?

this is my problem.

---------------------------

I try your 3 ways.

the first solution,do it exact.

thank you again.

night_p
11-19-2011, 04:01 AM
Now I have a file that was created with your guidance.

another problem is remain :

I have another file with 16200 row and 4 columns.

I should combine this 2 separate sheet to a one sheet.

how can I find and set the same value and del the others ?

Is it possible or not ?

The one way is to select one by one my aim value by press ctrl key and del the others. this way take a long.

night_p
11-19-2011, 04:04 AM
please tell me if my discreption is not clear,so I show my meaning by a pic

night_p
11-19-2011, 04:14 AM
we have an excell file contain sheet1 and sheet 2 and we need to create sheet 3 by combain these to sheets.


in sheet2 we have to many excess result( 16200 ) but we need only some of them (1914 ) that similar to the parameter Node ID in sheet 1. ( the problem is this 1914 value have a iregular position in sheet and not sorting by a rule )

we should select these simalar Node ID From sheet 2 and in final stage combain it with the sheet 1 and make a final result in sheet 3.

I hope somebody help me to do this.

you can download my excell file in attachment part:


http://picme.ir/images/27893603224706324563.jpg

mancubus
11-19-2011, 04:25 AM
hi.

this is defferent from the original thread requirement.

you should have started a new thread.

please open a new thread with ypur post above. and give it a proper title. "consolidate two worksheets. different table structure." could be an option.

nilem
11-19-2011, 04:29 AM
-----