VBA - check range in worksheet contains values

**Sophie02** · 07-31-2015, 05:42 AM

Hi

What would be the code to check a range of cells in a column contains any type of text, numbers etc, then if any of the cells do contain values input the values into one cell in a different worksheet.

Thanks in advance

**Parth007** · 07-31-2015, 06:38 AM

Can you provide an example.... any sample sheet with output

**bmouse** · 07-31-2015, 06:45 AM

From your initial post it seems you wish to copy many cells that contain data into a single cell in another worksheet?

**Sophie02** · 07-31-2015, 07:02 AM

I would ideally not want to post the workbook as it is confidential.

Yea I want to copy the range A4:A63 to a single cell in another worksheet, only if the range contains values. the reason for this is I don't want the procedure to run unless values are found in the range. Is this possible please.

**bmouse** · 07-31-2015, 07:09 AM

You can write a formula like this to pull the values in a single cell, regardless of content:

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14&A15&A16&A17&A18&A19&A20&A21&A22&A23

I abuse Word and Find&Replace for managing long formulas like this.

**Parth007** · 07-31-2015, 07:17 AM

Hi Sopie....

Sub Copy_Data()
  Application.ScreenUpdating = False
  Worksheets("Sheet2").Range("A4:A63").Value = Worksheets("Sheet1").Range("A4:A63").Value
  Application.ScreenUpdating = True
End Sub

**Sophie02** · 07-31-2015, 07:17 AM

I have tried that but I want to write it in vba as I have a load of other procedures to run along side that which will be assigned to a button.

**Sophie02** · 07-31-2015, 07:20 AM

Thank you Parth, although that's not exactly what im looking for.

I want to check a range to see if any values exist and then if they do copy the range of values to one cell.

**Parth007** · 07-31-2015, 07:32 AM

Any specific value to be checked in range?
or
you would rather see if the range have blanks cells, if so then pick only non blank cells & move to other sheet.. may be

**bmouse** · 07-31-2015, 07:36 AM

If one could figure out how to write this formula in VBA:
=IF(COUNTA(A1:A20)>=1;A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14&A15&A16&A17&A18&A19&A20)

**Parth007** · 07-31-2015, 08:50 AM

bmouse .. i m puzzled with the formula you wrote.. ofcourse that can be diverted via VBA but... transfer the data & etc...

**Sophie02** · 07-31-2015, 07:37 AM

No, no specific value which is annoying

Yes if the only way is to look for blank cells then copy then non blank cells that will be fine. Will be better than nothing.

**Parth007** · 07-31-2015, 09:09 AM

Hi Sophie...

you can see below ...(copy non blank cells to another worksheet)

Sub CopyDataOnly()
On Error Resume Next
Sheets("Sheet1").Range("A1:A63"). _
SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("Sheet2").Range("A1").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
On Error GoTo 0
Application.CutCopyMode = False
End Sub

OR

Sub CopyData()
    Dim rCell As Range, TestRange As Range, CopyRange As Range
     
    Set TestRange = Sheet1.Range("A1:A63")
     
    For Each rCell In TestRange
        If Not IsEmpty(rCell) Then
            If CopyRange Is Nothing Then
                Set CopyRange = rCell
            Else
                Set CopyRange = Union(CopyRange, rCell)
            End If
        End If
    Next rCell
     
    CopyRange.Copy Sheet2.Range("A1")
     
     
End Sub

**bmouse** · 07-31-2015, 08:55 AM

To my understanding the objective is to gather data from a range of cells into a single cell if there is ANY data in the range. So, COUNTA and IF check to see if even 1 cell from the range contains data. After that every cell in the range gets added together. Putting every cell in the formula is easier, because we can't really determine where the data is. Maybe it is in A1, maybe in A17, maybe both, etc., etc. The formula doesn't add anything if the cell is blank so it just gathers any and all data from the range into a single cell. Try it out, you will understand.

**Sophie02** · 08-03-2015, 03:42 AM

Thank you Parth, that is half of what I need.

What do I add to copy the values in the range in sheet 1 to a single cell in the sheet 2? As the current code gets rid of the blank cells and copies the range from sheet 1 but then pastes into sheet 2 but the values are as a list and in different rows instead of all being pasted into a single cell.

Thanks again.

**Parth007** · 08-03-2015, 03:54 AM

Hi Sophie,

Single cell means?
can you provide an example..

**Sophie02** · 08-03-2015, 04:09 AM

Hi sorry.

For example copy values from Sheet1.Range("A1:A63") to Sheet2.Range("A1")

With the code you previously suggested it copies the correct cells but that pastes into A1:A10. But I want all the values in Sheet1.Range("A1:A63") to be displayed in Sheet2.Range("A1"). So the text in cell A1 will look like;

A1 - Test 1 Test 2 Test 3 etc.

And not;

A1 - Test 1
A2 - Test 2
A3 - Test 3

Sorry if im not explaining it very well.

**bmouse** · 08-03-2015, 04:53 AM

Have you tried using the formula I posted? If you want to put many cells together in 1 cell, you can use the "&" method.

**Sophie02** · 08-03-2015, 04:55 AM

Yea I think that is going to be the best and quicker way to do it. Thank you

**Sophie02** · 08-03-2015, 05:35 AM

I have just realised that the reason I didn't want to do it that way is because between each cell I am putting a space, e.g = A1&" "&A2&" "&A3&" "&A4 and then when I perform an if function on the cell e.g. =if(A1<>"",.....) even if it hasn't got text (Test 1 Test 2) and is just ( ) as the function has spaces in it the =if function still performs as it is looking up the spaces in cell A1.

Do you know a way around this?

**bmouse** · 08-03-2015, 06:17 AM

Use this formula. If there are no cells with data within the range, the formula just returns FALSE. If you get many blank spaces, you can try to remove them with Ctrl+H.

Formula:

=IF(COUNTA(A4:A63)>=1;A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10&" "&A11&" "&A12&" "&A13&" "&A14&" "&A15&" "&A16&" "&A17&" "&A18&" "&A19&" "&A20&" "&A21&" "&A22&" "&A23&" "&A24&" "&A25&" "&A26&" "&A27&" "&A28&" "&A29&" "&A30&" "&A31&" "&A32&" "&A33&" "&A34&" "&A35&" "&A36&" "&A37&" "&A38&" "&A39&" "&A40&" "&A41&" "&A42&" "&A43&" "&A44&" "&A45&" "&A46&" "&A47&" "&A48&" "&A49&" "&A50&" "&A51&" "&A52&" "&A53&" "&A54&" "&A55&" "&A56&" "&A57&" "&A58&" "&A59&" "&A60&" "&A61&" "&A62&" "&A63)

**Sophie02** · 08-03-2015, 07:20 AM

Thank you bmouse will you that instead.

Thank you all for your help.

**Jacc** · 08-03-2015, 07:25 AM

Sophie, a good idea is to post a workbook with mockup data, just the minimum required to illustrate the problem. That would make it much easier to help you.

Hybrid View

VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Re: VBA - check range in worksheet contains values

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Q: check a range values

Bookmarks

Bookmarks

Posting Permissions