Number of columns needs to find out within range

PATTANAM

PATTANAM

Board Regular
Joined
Sep 28, 2011
Messages
132
Hi ALL,

I need to find out number of Columns in ActiveSheet in range ("A1:AD1"), I have written the code but it not working out.

My requirement within a Range ("A1: AD1") need to find out how many columns having a data, suppose data was updated less columns (less than 30 Columns) or more (More than 30 Columns) columns it should error.

Sub column()
Dim column As Long
column = ActiveSheet.Range("A1, AD1").Count
If column <> 30 Then
MsgBox ("Please check if there are any colums deleted or added, the file do not have standard number of colums"), vbInformation
Exit Sub
End If
End Sub




Please Please... help me on this.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
PATTANAM said:
Hi ALL,

I need to find out number of Columns in ActiveSheet in range ("A1:AD1"), I have written the code but it not working out.

My requirement within a Range ("A1: AD1") need to find out how many columns having a data, suppose data was updated less columns (less than 30 Columns) or more (More than 30 Columns) columns it should error.
Click to expand...

Maybe this...
Code: 
SubColumnCount()
  If WorksheetFunction.CountA(Range("A1:AD1")) <> 30 Then
    MsgBox "Please check if there are any colums deleted or added, the file do not have standard number of colums", vbInformation
  End If
End Sub
 
Upvote 0
PATTANAM,

Your original macro can only check the first 30 columns, A thru AD.

The following macro will find the last used column in row 1.

Try:

Code: 
Option Explicit
Sub CheckRow1ColumnCount()
' hiker95, 08/05/2013
' http://www.mrexcel.com/forum/excel-questions/718358-number-columns-needs-find-out-within-range.html
Dim lc As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
If WorksheetFunction.CountA(Range(Cells(1, 1), Cells(1, lc))) <> 30 Then
  MsgBox "Please check if there are any colums deleted or added, the file do not have standard number of colums", vbInformation
End If
End Sub
 
Upvote 0
PATTANAM,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
Thank you! I'm back now with one more help

With your great help I have used below code and modified as requested. </SPAN>
Sub Controls()
Dim wsCheck As Worksheet</SPAN>
On Error Resume Next
Set wsCheck = Sheets("Input JE Data")
If wsCheck Is Nothing Then
MsgBox ("Missing Input JE Data Sheet/Tab")
Exit Sub</SPAN>
Else
Sheets("Input JE Data").Select
Dim lc As Long
lc = Cells(1, Columns.Count).End(xlToLeft).column
If WorksheetFunction.CountA(Range(Cells(1, 1), Cells(1, lc))) <> 30 Then
MsgBox "Input JE Data Sheet/Tab doesn't have standard number of colums. Please check if there are any colums deleted or added", vbInformation
Exit Sub
End If
End If</SPAN>
Again I need check ones 30 column are there (as above code) then need to check same sheet columns are available or not which very important to me.</SPAN>
Like Column name: A= Final Index #</SPAN>
Column name: P= Charge out in local currency C = (A)*(B)</SPAN>
Column name: X= Cost Center (Expense) </SPAN>
Column name: Y= WBS Code (Expense)</SPAN>
Column name: AC= JV cost details.</SPAN>

Please… help me. Thank you!</SPAN>
 
Upvote 0
PATTANAM,

I am at a loss at your latest request.

In order to continue:

You can upload your workbook (including your macro code) to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Hi Hiker95,

Thanks for coming back to help!

I have uploaded file as you told and below is the link.

https://app.box.com/s/ou4ls832brh8plmk3p9r

Now we have checked 30 columns as our previouscode Now I need to check same sheet below mentioned columns are available or not which very important to me.</SPAN>
Like Column name: A= Final Index #</SPAN>
Column name: P= Charge out in local currency C = (A)*(B)</SPAN>
Column name: X= Cost Center (Expense) </SPAN>
Column name: Y= WBS Code (Expense)</SPAN>
Column name: AC= JV cost details.</SPAN>

Please… help me. Thank you!</SPAN>
 
Upvote 0
PATTANAM,

Thank you for the workbook.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

In your COPY of your workbook, replace the old/current Controls macro with the new one below.

I have put all the variables at the top of the macro code.

And, I have added the new section of code (for worksheet Input JE Data) between the following lines:
'************************************************************


Code: 
Option Explicit
Sub Controls()
' hiker95, 08/11/2013
' http://www.mrexcel.com/forum/excel-questions/718358-number-columns-needs-find-out-within-range.html
Dim wsCheck As Worksheet, wsCheck1 As Worksheet, wsCheck2 As Worksheet
Dim wsCheck3 As Worksheet, wsCheck4 As Worksheet, wsCheck5 As Worksheet
Dim lc As Long, lc1 As Long, lc2 As Long, lc3 As Long, lc4 As Long
Dim a As String, p As String, x As String, y As String, ac As String
On Error Resume Next
Set wsCheck = Sheets("Input JE Data")
If wsCheck Is Nothing Then
  MsgBox ("Missing 'Input JE Data' Sheet/Tab")
  Exit Sub
Else
  Sheets("Input JE Data").Select
  lc = Cells(1, Columns.Count).End(xlToLeft).Column
  If WorksheetFunction.CountA(Range(Cells(1, 1), Cells(1, lc))) <> 30 Then
    MsgBox "Input JE Data Sheet/Tab doesn't have standard number of colums. Please check if there are any colums deleted or added", vbInformation
    Exit Sub
  End If
End If

'************************************************************
'Check Sheets("Input JE Data") for the following columns:
'A  "Final Index #"
'P  "Charge out in local currencyC = (A)*(B)"
'X  "Cost Center(Expense)"
'Y  "WBS Code(Expense)"
'AC "JV cost details"
With Sheets("Input JE Data")
  a = "": p = "": x = "": y = "": ac = ""
  If InStr(.Cells(1, 1), "Final Index #") = 0 Then
    a = "Final Index #"
  End If
  If InStr(.Cells(1, 16), "Charge out" & vbLf & "in local currency" & vbLf & "C = (A)*(B)") = 0 Then
    p = "Charge out in local currency C = (A)*(B)"
  End If
  If InStr(.Cells(1, 24), "Cost Center" & vbLf & "(Expense)") = 0 Then
    x = "Cost Center(Expense)"
  End If
  If InStr(.Cells(1, 25), "WBS Code" & vbLf & "(Expense)") = 0 Then
    y = "WBS Code(Expense)"
  End If
  If InStr(.Cells(1, 29), "JV cost details") = 0 Then
    ac = "JV cost details"
  End If
  If a = "" And p = "" And x = "" And y = "" And ac = "" Then
    'do nothing
  Else
    MsgBox "The following titles in row 1 are missing/not correct: " & vbCrLf & vbCrLf & _
            a & vbCrLf & _
            p & vbCrLf & _
            x & vbCrLf & _
            y & vbCrLf & _
            ac & vbCrLf & ""
    Exit Sub
  End If
End With
'************************************************************

Set wsCheck1 = Sheets("Master Data for CoCo")
If wsCheck1 Is Nothing Then
  MsgBox ("Missing 'Master Data for CoCo' Sheet/Tab")
  Exit Sub
End If
Set wsCheck2 = Sheets("Relief CC")
If wsCheck2 Is Nothing Then
  MsgBox (" Missing 'Relief CC' Sheet/Tab")
  Exit Sub
Else
  Sheets("Relief CC").Select
  lc1 = Cells(1, Columns.Count).End(xlToLeft).Column
  If WorksheetFunction.CountA(Range(Cells(1, 1), Cells(1, lc1))) <> 47 Then
    MsgBox "'Relief CC' Sheet/Tab doesn't have standard number of colums. Please check if there are any colums deleted or added", vbInformation
  Exit Sub
  End If
End If
Set wsCheck3 = Sheets("Expense WBS")
If wsCheck3 Is Nothing Then
  MsgBox ("Missing 'Expense WBS' Sheet/Tab")
  Exit Sub
Else
  Sheets("Expense WBS").Select
  lc2 = Cells(1, Columns.Count).End(xlToLeft).Column
  If WorksheetFunction.CountA(Range(Cells(1, 1), Cells(1, lc2))) <> 18 Then
    MsgBox "'Expense WBS' Sheet/Tab doesn't have standard number of colums. Please check if there are any colums deleted or added", vbInformation
    Exit Sub
  End If
End If
Set wsCheck4 = Sheets("Expense CC")
If wsCheck4 Is Nothing Then
  MsgBox ("Missing 'Expense CC' Sheet/Tab")
  Exit Sub
Else
  Sheets("Expense CC").Select
  lc3 = Cells(1, Columns.Count).End(xlToLeft).Column
  If WorksheetFunction.CountA(Range(Cells(1, 1), Cells(1, lc3))) <> 47 Then
    MsgBox "'Expense CC' Sheet/Tab doesn't have standard number of colums. Please check if there are any colums deleted or added", vbInformation
  Exit Sub
  End If
End If
Set wsCheck5 = Sheets("Expense IO")
If wsCheck5 Is Nothing Then
  MsgBox ("Missing 'Expense IO' Sheet/Tab")
  Exit Sub
Else
  Sheets("Expense IO").Select
  lc4 = Cells(1, Columns.Count).End(xlToLeft).Column
  If WorksheetFunction.CountA(Range(Cells(1, 1), Cells(1, lc4))) <> 47 Then
    MsgBox "'Expense IO' Sheet/Tab doesn't have standard number of colums. Please check if there are any colums deleted or added", vbInformation
  Exit Sub
  End If
End If
On Error GoTo 0
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the new Controls macro.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA copy Values exclude 1 column and formula
Replies
2
Views
271
bombergirl61
B
T
  • Solved
Multiple columns into 2?
Replies
6
Views
260
Georgiboy
Georgiboy
S
  • Solved
Return error if "Filter" doesn't return the required entry
Replies
2
Views
107
skittlz
S
H
  • Question Question
Need to add column header values to this code
Replies
1
Views
47
Osvaldo Palmeiro
O
D
  • Solved
VBA - Select a range of cells with keystrokes but don't want to see the cells
Replies
6
Views
288
Dan2k
D

Forum statistics

Threads
1,223,099
Messages
6,170,114
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top