# Excel No Responding

• ### Question

• Dear Sir:

Sub GetConcentrationData()
Dim sht1 As Worksheet, sht As Worksheet
Dim i As Long, j As Long, rowCount As Long, rowCount1 As Long

Set sht = ThisWorkbook.Sheets(1)
rowCount = sht.UsedRange.Rows.Count

With sht1
.Name = "ConcentrationData"
End With
sht1.Range("A1") = "Time"
sht1.Range("B1") = "Area No"
sht1.Range("C1") = "Gas No"
sht1.Range("D1") = "Concentration"
rowCount1 = sht1.UsedRange.Rows.Count

Application.ScreenUpdating = False
For i = 1 To rowCount
If sht.UsedRange.Columns(7).Rows(i) = "concentration" Then

sht1.Range("A" & rowCount1 + 1) = sht.UsedRange.Cells(i, 1)
sht1.Range("B" & rowCount1 + 1) = sht.UsedRange.Cells(i, 4)
sht1.Range("C" & rowCount1 + 1) = sht.UsedRange.Cells(i, 6)
sht1.Range("D" & rowCount1 + 1) = sht.UsedRange.Cells(i, 9)

rowCount1 = rowCount1 + 1
End If
Next i

End Sub

When I debuged it, it ran without any problem. But when I ran this macro, the excel has no responding. Would you please help see where the problem of my procedure is and how to solve this problem?

Regards,

Shell Hu

Tuesday, April 11, 2017 4:49 AM

• Dear Sir:

I revised my sub a little as following:

Sub GetConcentrationData()
Dim sht1 As Worksheet, sht As Worksheet
Dim i As Long, j As Long, rowCount As Long, rowCount1 As Long
Set sht = Sheets("Sheet1")
rowCount = sht.UsedRange.Rows.Count
With sht1
.Name = "ConcentrationData"
End With
sht1.Range("A1") = "Time"
sht1.Range("B1") = "Area No"
sht1.Range("C1") = "Gas No"
sht1.Range("D1") = "Concentration"
rowCount1 = sht1.UsedRange.Rows.Count
For i = 1 To rowCount
If sht.Cells(i, 7) = "concentration" Then
sht1.Range("A" & rowCount1 + 1) = sht.Cells(i, 1)
sht1.Range("B" & rowCount1 + 1) = sht.Cells(i, 4)
sht1.Range("C" & rowCount1 + 1) = sht.Cells(i, 6)
sht1.Range("D" & rowCount1 + 1) = sht.Cells(i, 9)
rowCount1 = rowCount1 + 1
End If
Next i
End Sub

Now this sub works fine for me.

Regards,

Shell Hu

• Marked as answer by Wednesday, April 12, 2017 9:59 AM
Wednesday, April 12, 2017 9:58 AM

### All replies

• Hi Shell Hu,

How did you execute Sub "GetConcentrationData"?

I don't know what you want to do, but your code works fine.
I made a button on Sheet1. The button.Click calls your Sub "GetConcentrationData".

```Private Sub btn_GetConcentrationData_Click()
Dim i As Integer
For i = 1 To Worksheets.Count
If (Worksheets(i).Name = "ConcentrationData") Then
MsgBox "worksheet named [ConcentrationData] exists already"
Exit Sub
End If
Next
' ---
Call GetConcentrationData
End Sub

Sub GetConcentrationData()
Dim sht1 As Worksheet, sht As Worksheet
Dim i As Long, j As Long, rowCount As Long, rowCount1 As Long    ....End Sub```
______________
Ashidacchi
• Edited by Tuesday, April 11, 2017 5:54 AM
Tuesday, April 11, 2017 5:52 AM
• Dear Sir:

I just want to copy some of the data into a new worksheet. I call this sub on the active sheet with existing data.

Regards,

Shell Hu

Tuesday, April 11, 2017 6:04 AM
• Dear Shell Hu,

I want to know the meaning of "Excel No Responding".
I could make your code executed, and your code worked. So, I asked how you did to execute it.
Could you explain in detail about "I call this sub on the active sheet"?

Regards,
Hideki Ashida

• Edited by Tuesday, April 11, 2017 6:23 AM
Tuesday, April 11, 2017 6:10 AM
• Dear Sir:

Maybe I'm not describing the issue clearly. When I call this sub, the excel seems stopped running. It shows "no responding" on the top of excel title. （I just called the sub like you described)

Regards,

Shell Hu

Tuesday, April 11, 2017 6:31 AM
• Thank you for explanation.
I understand it and another question hit on me.
What version/build of Excel and Windows are you using?

I've shared an Excel file including your code via Dropbox.
https://www.dropbox.com/s/q3m0k2yzp0nvbmd/Excel%20GetConcentrationData.xlsm?dl=0

# before click button [GetConcentrationData], delete the first sheet [ConcentrationData].
____________
Ashidacchi

• Edited by Tuesday, April 11, 2017 7:10 AM
Tuesday, April 11, 2017 6:39 AM
• Dear Sir:

I'm using excel2016 and windows10.

Regards,

Shell Hu

Tuesday, April 11, 2017 7:07 AM
• Hi,

Oh! It's the same as my environment.
___________
Ashidacchi
Tuesday, April 11, 2017 7:09 AM
• Dear Sir:

I cannot reach the dropbox you provided.

Regards,

Shell Hu

Tuesday, April 11, 2017 7:31 AM
• Hello Shell,

Do you use the same data when debugging and running?

The macro works fine for me too. I think the issue casues from your data. Please create a new workbook with little data to check if the issue still exists. If we could narrow down the issue does cause from your current data, I suggest you share your file here.

Regards,

Celeste

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Wednesday, April 12, 2017 5:46 AM
• Hello Shell,

I sent you an email with one attached file yesterday, but I could hear nothing from you. What's wrong?
_____________
Ashidacchi
• Edited by Wednesday, April 12, 2017 7:08 AM
Wednesday, April 12, 2017 7:00 AM
• Dear Sir:

I revised my sub a little as following:

Sub GetConcentrationData()
Dim sht1 As Worksheet, sht As Worksheet
Dim i As Long, j As Long, rowCount As Long, rowCount1 As Long
Set sht = Sheets("Sheet1")
rowCount = sht.UsedRange.Rows.Count
With sht1
.Name = "ConcentrationData"
End With
sht1.Range("A1") = "Time"
sht1.Range("B1") = "Area No"
sht1.Range("C1") = "Gas No"
sht1.Range("D1") = "Concentration"
rowCount1 = sht1.UsedRange.Rows.Count
For i = 1 To rowCount
If sht.Cells(i, 7) = "concentration" Then
sht1.Range("A" & rowCount1 + 1) = sht.Cells(i, 1)
sht1.Range("B" & rowCount1 + 1) = sht.Cells(i, 4)
sht1.Range("C" & rowCount1 + 1) = sht.Cells(i, 6)
sht1.Range("D" & rowCount1 + 1) = sht.Cells(i, 9)
rowCount1 = rowCount1 + 1
End If
Next i
End Sub

Now this sub works fine for me.

Regards,

Shell Hu

• Marked as answer by Wednesday, April 12, 2017 9:59 AM
Wednesday, April 12, 2017 9:58 AM
• Hi Shell Hu,