Delete column if a cell is not named
 

I would like to do the following within VBA:
- Select range from first cell to last cell in first row of a database.
- Find arguments in this range (e.g. if cell value = "Sales" then name the
cell = "Sales"). I have several arguments.
- Afterwards, I want to delete all columns that do not contain these
arguments (in the example above: If the first cell of a column is not
"Sales", then delete the column).

My main problem: How do I check if a cell is named or not?

Thank you in advance!
Mirja

Delete column if a cell is not named
 

Hi Mirja,

Sub test()
On Error Resume Next
sName = ""
sName = Range("A1").Name.Name
On Error GoTo 0
If sRangeName = "" Then
MsgBox "not named"
Else
MsgBox sName
End If
End Sub

Regards,
Peter T

"Mirja" wrote in message
...
I would like to do the following within VBA:
- Select range from first cell to last cell in first row of a database.
- Find arguments in this range (e.g. if cell value = "Sales" then name the
cell = "Sales"). I have several arguments.
- Afterwards, I want to delete all columns that do not contain these
arguments (in the example above: If the first cell of a column is not
"Sales", then delete the column).

My main problem: How do I check if a cell is named or not?

Thank you in advance!
Mirja

Delete column if a cell is not named
 

Hi Peter,
thanks for replying. Unfortunately, this did not work. The command is for a
special name, isn't it? But I like to check all cells of the first row for
ANY name. So if a cell has any name, then don't delete the column, if a cell
is not named, delete the column ... Do you know how to do that?
Thanks again!
Mirja

"Peter T" wrote:

Hi Mirja,

Sub test()
On Error Resume Next
sName = ""
sName = Range("A1").Name.Name
On Error GoTo 0
If sRangeName = "" Then
MsgBox "not named"
Else
MsgBox sName
End If
End Sub

Regards,
Peter T

"Mirja" wrote in message
...
I would like to do the following within VBA:
- Select range from first cell to last cell in first row of a database.
- Find arguments in this range (e.g. if cell value = "Sales" then name the
cell = "Sales"). I have several arguments.
- Afterwards, I want to delete all columns that do not contain these
arguments (in the example above: If the first cell of a column is not
"Sales", then delete the column).

My main problem: How do I check if a cell is named or not?

Thank you in advance!
Mirja

Delete column if a cell is not named
 

The example code is not looking for a "special name", it simply returns the
name of A1 if it has a name, otherwise the empty string confirms A1 is not a
named cell.

If I understand your original correctly you want to delete entire columns of
header cells that are not named. Following should delete all columns between
B1:J1 (header range) if the cell in row 1 is not a named cell.

Sub test3()
Dim col As Long
Dim rHeaders As Range

Set rHeaders = ActiveSheet.Range("B1:J1")
For col = rHeaders.Count To 1 Step -1
On Error Resume Next
sname = ""
sname = rHeaders(1, col).Name.Name
On Error GoTo 0

If sname = "" Then
ActiveSheet.Columns(rHeaders(1, col).Column).Delete
End If
Next
End Sub

Use with caution, might accidently delete data if the header cell is not
named.

Regards,
Peter T



"Mirja" wrote in message
...
Hi Peter,
thanks for replying. Unfortunately, this did not work. The command is for
a
special name, isn't it? But I like to check all cells of the first row for
ANY name. So if a cell has any name, then don't delete the column, if a
cell
is not named, delete the column ... Do you know how to do that?
Thanks again!
Mirja

"Peter T" wrote:

Hi Mirja,

Sub test()
On Error Resume Next
sName = ""
sName = Range("A1").Name.Name
On Error GoTo 0
If sRangeName = "" Then
MsgBox "not named"
Else
MsgBox sName
End If
End Sub

Regards,
Peter T

"Mirja" wrote in message
...
I would like to do the following within VBA:
- Select range from first cell to last cell in first row of a
database.
- Find arguments in this range (e.g. if cell value = "Sales" then name
the
cell = "Sales"). I have several arguments.
- Afterwards, I want to delete all columns that do not contain these
arguments (in the example above: If the first cell of a column is not
"Sales", then delete the column).

My main problem: How do I check if a cell is named or not?

Thank you in advance!
Mirja

Delete column if a cell is not named
 

Thanks, Peter! Now it worked out :-)

"Peter T" wrote:

The example code is not looking for a "special name", it simply returns the
name of A1 if it has a name, otherwise the empty string confirms A1 is not a
named cell.

If I understand your original correctly you want to delete entire columns of
header cells that are not named. Following should delete all columns between
B1:J1 (header range) if the cell in row 1 is not a named cell.

Sub test3()
Dim col As Long
Dim rHeaders As Range

Set rHeaders = ActiveSheet.Range("B1:J1")
For col = rHeaders.Count To 1 Step -1
On Error Resume Next
sname = ""
sname = rHeaders(1, col).Name.Name
On Error GoTo 0

If sname = "" Then
ActiveSheet.Columns(rHeaders(1, col).Column).Delete
End If
Next
End Sub

Use with caution, might accidently delete data if the header cell is not
named.

Regards,
Peter T



"Mirja" wrote in message
...
Hi Peter,
thanks for replying. Unfortunately, this did not work. The command is for
a
special name, isn't it? But I like to check all cells of the first row for
ANY name. So if a cell has any name, then don't delete the column, if a
cell
is not named, delete the column ... Do you know how to do that?
Thanks again!
Mirja

"Peter T" wrote:

Hi Mirja,

Sub test()
On Error Resume Next
sName = ""
sName = Range("A1").Name.Name
On Error GoTo 0
If sRangeName = "" Then
MsgBox "not named"
Else
MsgBox sName
End If
End Sub

Regards,
Peter T

"Mirja" wrote in message
...
I would like to do the following within VBA:
- Select range from first cell to last cell in first row of a
database.
- Find arguments in this range (e.g. if cell value = "Sales" then name
the
cell = "Sales"). I have several arguments.
- Afterwards, I want to delete all columns that do not contain these
arguments (in the example above: If the first cell of a column is not
"Sales", then delete the column).

My main problem: How do I check if a cell is named or not?

Thank you in advance!
Mirja

Delete column if a cell is not named
 

For completeness I see I forgot to declare the variable 'sname'

Dim sName As String

Regards,
Peter T

PS glad you got it working


"Peter T" <peter_t@discussions wrote in message
...
The example code is not looking for a "special name", it simply returns
the
name of A1 if it has a name, otherwise the empty string confirms A1 is not
a
named cell.

If I understand your original correctly you want to delete entire columns
of
header cells that are not named. Following should delete all columns
between
B1:J1 (header range) if the cell in row 1 is not a named cell.

Sub test3()
Dim col As Long
Dim rHeaders As Range

Set rHeaders = ActiveSheet.Range("B1:J1")
For col = rHeaders.Count To 1 Step -1
On Error Resume Next
sname = ""
sname = rHeaders(1, col).Name.Name
On Error GoTo 0

If sname = "" Then
ActiveSheet.Columns(rHeaders(1, col).Column).Delete
End If
Next
End Sub

Use with caution, might accidently delete data if the header cell is not
named.

Regards,
Peter T



"Mirja" wrote in message
...
Hi Peter,
thanks for replying. Unfortunately, this did not work. The command is
for
a
special name, isn't it? But I like to check all cells of the first row
for
ANY name. So if a cell has any name, then don't delete the column, if a
cell
is not named, delete the column ... Do you know how to do that?
Thanks again!
Mirja

"Peter T" wrote:

Hi Mirja,

Sub test()
On Error Resume Next
sName = ""
sName = Range("A1").Name.Name
On Error GoTo 0
If sRangeName = "" Then
MsgBox "not named"
Else
MsgBox sName
End If
End Sub

Regards,
Peter T

"Mirja" wrote in message
...
I would like to do the following within VBA:
- Select range from first cell to last cell in first row of a
database.
- Find arguments in this range (e.g. if cell value = "Sales" then
name
the
cell = "Sales"). I have several arguments.
- Afterwards, I want to delete all columns that do not contain these
arguments (in the example above: If the first cell of a column is
not
"Sales", then delete the column).

My main problem: How do I check if a cell is named or not?

Thank you in advance!
Mirja