Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 28th 09, 02:18 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 5
Default macro for deleting cells trought column A

hello!

my problem begins with a lot of data.
i need macro to delete all number values in A columns and a entire
row.
or all cell which begins with a certain number (1,...,9).

tnx.

  #2   Report Post  
Old January 28th 09, 02:40 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default macro for deleting cells trought column A

Hi,

You weren't specific about what the number was to begin with so this uses 9.
Change to suit.

Right click your sheet tab, view code and paste this in and run it.

Sub delete_Me()
Dim CopyRange As Range, MyRange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each C In MyRange
If Left(C.Value, 1) = 9 Then
If CopyRange Is Nothing Then
Set CopyRange = C.EntireRow
Else
Set CopyRange = Union(CopyRange, C.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If
End Sub

Mike

"tabađija" wrote:

hello!

my problem begins with a lot of data.
i need macro to delete all number values in A columns and a entire
row.
or all cell which begins with a certain number (1,...,9).

tnx.

  #3   Report Post  
Old January 28th 09, 03:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 1
Default macro for deleting cells trought column A

tnx MAN...U RESCUE ME...my fingers almost palayse.

do u have idea how to make a SUM(cell A) by the same Atribut's and
then delete all but one atribut and leave the SUM


befo

ALBANIJA 211
AUSTRIJA 4260
AUSTRIJA 28274
AUSTRIJA 12696
AUSTRIJA 1199671
AUSTRIJA 12870
AUSTRIJA 13926
AUSTRIJA 406254
AUSTRIJA 249971
AUSTRIJA 89479
AUSTRIJA 106262
AUSTRIJA 2020476
BELGIJA 56070
BELGIJA 543
BELGIJA 35401
BELGIJA 202120
BOSNA I HERCEGOVINA 31718
BOSNA I HERCEGOVINA 26061
BOSNA I HERCEGOVINA 58798
BOSNA I HERCEGOVINA 54642
BOSNA I HERCEGOVINA 28931
BOSNA I HERCEGOVINA 2781
BOSNA I HERCEGOVINA 868
BOSNA I HERCEGOVINA 7422
BOSNA I HERCEGOVINA 9658
BOSNA I HERCEGOVINA 16383
BOSNA I HERCEGOVINA 152
BOSNA I HERCEGOVINA 172
BOSNA I HERCEGOVINA 2299
BOSNA I HERCEGOVINA 6775
BOSNA I HERCEGOVINA 17237
BOSNA I HERCEGOVINA 945
BOSNA I HERCEGOVINA 2248
BOSNA I HERCEGOVINA 1174
BOSNA I HERCEGOVINA 19470

after:

ALBANIJA 211
AUSTRIJA 4260
28274
12696
1199671
12870
13926
406254
249971
89479
106262
2020476
BELGIJA 56070
543
35401
202120
BOSNA I HERCEGOVINA 31718
26061
58798
54642
28931
2781
868
7422
9658
16383
152
172


  #4   Report Post  
Old January 28th 09, 04:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default macro for deleting cells trought column A

Producing the list with deleted duplicate country names can be done with
this macro (change the two Const statements and the single With statement to
reflect your actual data set up)...

Sub DeleteDuplicateCountryNames()
Dim C As Range
Dim X As Long
Dim LastRow As Long
Const DataStartRow As Long = 3
Const DataColumn As String = "A"
With Worksheets("Sheet6")
LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For X = LastRow To DataStartRow - 1 Step -1
Set C = .Cells(X, DataColumn)
If InStr(C.Offset(-1).Value, Left(C.Value, _
InStrRev(C.Value, " "))) = 1 Then
C.Value = Mid(C.Value, InStrRev(C.Value, " ") + 1)
End If
Next
End With
End Sub

I would add the summation you want to the macro, but you didn't make clear
what you wanted summed up (the individual sums per country or the sum for
all the countries in the list). Which did you want? Also, where did you want
this or these sums placed at?

--
Rick (MVP - Excel)


"tabadija" wrote in message
...
tnx MAN...U RESCUE ME...my fingers almost palayse.

do u have idea how to make a SUM(cell A) by the same Atribut's and
then delete all but one atribut and leave the SUM


befo

ALBANIJA 211
AUSTRIJA 4260
AUSTRIJA 28274
AUSTRIJA 12696
AUSTRIJA 1199671
AUSTRIJA 12870
AUSTRIJA 13926
AUSTRIJA 406254
AUSTRIJA 249971
AUSTRIJA 89479
AUSTRIJA 106262
AUSTRIJA 2020476
BELGIJA 56070
BELGIJA 543
BELGIJA 35401
BELGIJA 202120
BOSNA I HERCEGOVINA 31718
BOSNA I HERCEGOVINA 26061
BOSNA I HERCEGOVINA 58798
BOSNA I HERCEGOVINA 54642
BOSNA I HERCEGOVINA 28931
BOSNA I HERCEGOVINA 2781
BOSNA I HERCEGOVINA 868
BOSNA I HERCEGOVINA 7422
BOSNA I HERCEGOVINA 9658
BOSNA I HERCEGOVINA 16383
BOSNA I HERCEGOVINA 152
BOSNA I HERCEGOVINA 172
BOSNA I HERCEGOVINA 2299
BOSNA I HERCEGOVINA 6775
BOSNA I HERCEGOVINA 17237
BOSNA I HERCEGOVINA 945
BOSNA I HERCEGOVINA 2248
BOSNA I HERCEGOVINA 1174
BOSNA I HERCEGOVINA 19470

after:

ALBANIJA 211
AUSTRIJA 4260
28274
12696
1199671
12870
13926
406254
249971
89479
106262
2020476
BELGIJA 56070
543
35401
202120
BOSNA I HERCEGOVINA 31718
26061
58798
54642
28931
2781
868
7422
9658
16383
152
172



  #5   Report Post  
Old January 28th 09, 08:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 5
Default macro for deleting cells trought column A

On Jan 28, 4:46*pm, "Rick Rothstein"
wrote:
Producing the list with deleted duplicate country names can be done with
thismacro(change the two Const statements and the single With statement to
reflect your actual data set up)...

Sub DeleteDuplicateCountryNames()
* Dim C As Range
* Dim X As Long
* Dim LastRow As Long
* Const DataStartRow As Long = 3
* Const DataColumn As String = "A"
* With Worksheets("Sheet6")
* * LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
* * For X = LastRow To DataStartRow - 1 Step -1
* * * Set C = .Cells(X, DataColumn)
* * * If InStr(C.Offset(-1).Value, Left(C.Value, _
* * * * * * * * * InStrRev(C.Value, " "))) = 1 Then
* * * * C.Value = Mid(C.Value, InStrRev(C.Value, " ") + 1)
* * * End If
* * Next
* End With
End Sub

I would add the summation you want to the macro, but you didn't make clear
what you wanted summed up (the individual sums per country or the sum for
all the countries in the list). Which did you want? Also, where did you want
this or these sums placed at?


I dont understand what need to change in

Const DataStartRow As Long = 3 - this dont understand
Const DataColumn As String = "A" - this understand

With Worksheets("Sheet6") -this understand(place name
of my Sheet)

BTW tnx very much
i need individual sums per country and then delete unwanted
country's.
need only summation and one country


  #6   Report Post  
Old January 28th 09, 09:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default macro for deleting cells trought column A

See inline comments...

I dont understand what need to change in

Const DataStartRow As Long = 3 - this dont understand


Put the row number of your first piece of data in the DataStartRow constant.
For your posted example data, that would be the row number where "ALBANIJA
211" is located. I don't know your layout (do you have header rows for
example... if so, do you have a blank separating row between your header and
first piece of data?)... so you have to tell the code where your data is.

i need individual sums per country and then delete unwanted country's.
need only summation and one country


But where do you want these sums to go? In an array in memory? On the
worksheet somewhere? If on the worksheet, where (what column and/or row)?

And what do you mean by "delete unwanted country's"? What makes a country
wanted or unwanted?

--
Rick (MVP - Excel)

  #7   Report Post  
Old January 28th 09, 11:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 5
Default macro for deleting cells trought column A

On Jan 28, 9:04*pm, "Rick Rothstein"
wrote:
See inline comments...

I dont understand what need to change in


* Const DataStartRow As Long = 3 * * *- this dont understand


Put the row number of your first piece of data in the DataStartRow constant.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching Column and deleting cells -VBA TLuebke Excel Programming 2 July 10th 08 07:32 PM
Deleting Duplicate Cells in one Column. Jamie Excel Discussion (Misc queries) 6 November 20th 07 05:40 PM
Deleting blank cells in a column bodhisatvaofboogie Excel Programming 1 June 13th 06 08:20 PM
How to quit from Powerpoint launched trought VBA codes in Excel Dennis Excel Programming 6 March 17th 06 04:13 PM
Deleting blank cells in a column KatyLady Excel Discussion (Misc queries) 6 May 30th 05 03:47 PM


All times are GMT +1. The time now is 02:38 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017